What is "Wishes" and what are they for?
PostgreSQL database development
Hello Guest
  
  • Login
• Register…
• Start blog
  • Who, Where, When
• What is interesting here?
• Duels
  • Polls
• Avatars
• Interests
  • Cities and Countries
• Random blog
• Users search
  • Search
• Games
• Tests
• QAIX
  • Сообщества
• Talxy Chat
• Horoscope
• Online
 
Register!

QAIX > PostgreSQL database developmentGo to page: « previous | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | next »

  Top users: 
  Recent blog posts: 
  They have birthday today: 
  Forums:   
  Discuss: 
  Recent forum topics: 
  Recent forum comments:
  Модератор:
Saturday, 27 January 2007
[pgsql-patches] pltcl/plython fixes for spi_prepare types Andrew Dunstan 22:47:35
 
Here's a patch along the same lines as the fix for plperl committed
earlier today, that allows passing type aliases to spi_prepare as well
as types named in pg_type. It also removes the mention of the previous
limitation in the pltcl docs. Unlike the plperl and pltcl cases, I
didn't use the simpler form that Tom suggested for plpython, as that
code wants to get hold of the HeapTuple. If anyone wants to tidy that up
some, feel free. Also, some regression tests from those with more tcl-fu
or python-fu that I have would be nice.

I'll apply this in a day or two unless there's an objection.

cheers

andrew



-------------------­--------(end of broadcast)---------­------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Add comment
Re: Predicted lifespan of different PostgreSQL Bruce Momjian 21:51:17
 Devrim GUNDUZ wrote:
-- Start of PGP signed section.> Hi,>
On Sat, 2007-01-27 at 08:41 +0000, Dave Page wrote:> >
What do people think about that? Does anyone think it would be an> > unreasonable policy? >
I don't think so. You should build 8.0 binaries until the community> stops maintaining PostgreSQL 8.0.>
This is what we do for RPMs -- I know it is really a lot of work to> maintain older releases but as a community packager, it is not my "job"> to force people to upgrade to new version by not supplying older> binaries.

Agreed, and I do believe we should be supporting releases as far back as
reasonably possible.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterpri­sedb.com

+ If your life is a hard drive, Christ can be your backup. +

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 2 answer | Add comment
windows/ecpg regression tests failing Andrew Dunstan 21:35:42
 
We seem to have 2 ECPG regression failures on Windows - see below, taken
from buildfarm log. Can we either fix ecpg or fix the expected results?

cheers

andrew

*** expected/compat_inf­ormix-dec_test-MinGW­32.stdoutSat Jan 27 02:34:46 2007
--- results/compat_info­rmix-dec_test.stdout­Sat Jan 27 02:59:35 2007
***************
*** 69,75 ****
dec[6,7]: 0.00 (r: 0 - cmp: 1)
dec[6,8]: 0 (r: 0)
dec[6,9]: 0.00 (r: 0 - cmp: 1)
- (errno == PGTYPES_NUM_UNDERFL­OW) - dec[6,10]: 0 (r: -1)

dec[7,1]: r: 0, 0.001
dec[7,2]: r: 0, 0
--- 69,74 ----
*** expected/pgtypeslib­-num_test2-MinGW32.s­tdoutSat Jan 27 02:34:46 2007
--- results/pgtypeslib-­num_test2.stdoutSat Jan 27 02:59:43 2007
***************
*** 88,94 ****
num[6,7]: 0.00 (r: 0 - cmp: 1)
num[6,8]: 0 (r: 0)
num[6,9]: 0.00 (r: 0 - cmp: 1)
- (errno == PGTYPES_NUM_UNDERFL­OW) - num[6,10]: 0 (r: -1)
num[6,11]: - (r: 0)
num[6,12]: 0.00 (r: 0 - cmp: 0)

--- 88,93 ----






-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 1 answer | Add comment
Re: How to configure Postgres to make it not to use (load) Joshua D. Drake 21:02:33
 Tom Dong wrote:> Sorry for the spam. I am not sure if the email I sent earlier went> though as it was before I signed up for this email list.>
Hi,>
I am looking for a way via configuration to make Postgres> not to use the openssl lib libeay32.dll as I need to delete that> library. I basically need to remove any encryption (hash is fine)> features (buid-in functions are ok) from my Postgres (8.x) installation.> This is quite urgent for me. I would be very grateful if someone in> this communicate can reply to this email and help me.


You can download the source and recompile without the ssl flag.

Sincerely,

Joshua D. Drake

Thanks!>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandp­rompt.com/

Donate to the PostgreSQL Project: http://www.postgres­ql.org/about/donate
PostgreSQL Replication: http://www.commandp­rompt.com/products/


-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

Add comment
How to configure Postgres to make it not to use (load) opensll crypto libraries. Tom Dong 20:57:13
 Sorry for the spam. I am not sure if the email I sent earlier went
though as it was before I signed up for this email list.





Hi,



I am looking for a way via configuration to make Postgres
not to use the openssl lib libeay32.dll as I need to delete that
library. I basically need to remove any encryption (hash is fine)
features (buid-in functions are ok) from my Postgres (8.x) installation.
This is quite urgent for me. I would be very grateful if someone in
this communicate can reply to this email and help me.



Thanks!

Tom







Add comment
Modifying and solidifying contrib Joshua D. Drake 20:34:12
 Hello,

With all the recent discussion on contrib modules etc.. I would like to
offer the following suggestion. I am willing to do a good portion of the
work myself and I can get it done before feature freeze. I will need
help with the global make file stuff however so that is one dependency.

Add directory /modules
Modules are compiled and installed by default but not enabled.
Modules in 8.3 currently are:
pgrowlocks
pg_freespacemap (to be renameed pgfreespacemap to be consistent)
pgstattuple
pgcrypto
xml2
pgbuffercache
initagg

Requirements for /modules
Must go through normal vetting process on -hackers
Must include patches to core documentation in Docbook
Must include test cases? I don't recall if we have regress for all
contrib stuff.

Keep directory contrib
Contrib is not compiled or installed by default
Contrib in 8.3 would be:
start-scripts
pgbench (which I think really should be a foundry project)
vacuumlo (is this even required anymore?)
adminpack
btree_gist
etc...
Requirements for /contrib
Must go through normal vetting process on -hackers
Must includes README
Must include test cases? Same questions for modules


Thoughts, flames?

Sincerely,

Joshua D. Drake






--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandp­rompt.com/

Donate to the PostgreSQL Project: http://www.postgres­ql.org/about/donate
PostgreSQL Replication: http://www.commandp­rompt.com/products/


-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate

comment 2 answer | Add comment
Predicted lifespan of different PostgreSQL branches Bill Moran 20:14:47
 
I spend some time googling this and searching the Postgresql.org site, but
I'm either not good enough with the search strings, or it's not to be found.

I'm trying to plan upgrades so that we don't upgrade needlessly, but also
don't get caught using stuff that nobody's supporting any more.
The FreeBSD project keeps this schedule:
http://www.freebsd.­org/security/#adv
which is _really_ nice when talking to managers and similar people about
when upgrades need to be scheduled.

Does the PostgreSQL project have any similar policy about EoLs? Even just
a simple statement like, "it is our goal to support major branches for 2
years after release" or some such?

--
Bill Moran
Collaborative Fusion Inc.

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 9 answers | Add comment
Converting 7.x to 8.x Carlos 18:45:16
 What would be the faster way to convert a 7.4.x database into an 8.x
database? A dump of the database takes over 20 hours so we want to convert
the database without having to do a dump and resptore.

comment 6 answers | Add comment
Proposal: Commit timestamp Jan Wieck 17:14:02
 For a future multimaster replication system, I will need a couple of
features in the PostgreSQL server itself. I will submit separate
proposals per feature so that discussions can be kept focused on one
feature per thread.

For conflict resolution purposes in an asynchronous multimaster system,
the "last update" definition often comes into play. For this to work,
the system must provide a monotonically increasing timestamp taken at
the commit of a transaction. During replication, the replication process
must be able to provide the remote nodes timestamp so that the
replicated data will be "as of the time it was written on the remote
node", and not the current local time of the replica, which is by
definition of "asynchronous" later.

To provide this data, I would like to add another "log" directory,
pg_tslog. The files in this directory will be similar to the clog, but
contain arrays of timestamptz values. On commit, the current system time
will be taken. As long as this time is lower or equal to the last taken
time in this PostgreSQL instance, the value will be increased by one
microsecond. The resulting time will be added to the commit WAL record
and written into the pg_tslog file.

If a per database configurable tslog_priority is given, the timestamp
will be truncated to milliseconds and the increment logic is done on
milliseconds. The priority is added to the timestamp. This guarantees
that no two timestamps for commits will ever be exactly identical, even
across different servers.

The COMMIT syntax will get extended to

COMMIT [TRANSACTION] [WITH TIMESTAMP <timestamptz>];

The extension is limited to superusers and will override the normally
generated commit timestamp. This will be used to give the replicating
transaction on the replica the exact same timestamp it got on the
originating master node.

The pg_tslog segments will be purged like the clog segments, after all
transactions belonging to them have been stamped frozen. A frozen xid by
definition has a timestamp of epoch. To ensure a system using this
timestamp feature has enough time to perform its work, a new GUC
variable defining an interval will prevent vacuum from freezing xid's
that are younger than that.

A function get_commit_timestam­p(xid) returning timpstamptz will return
the commit time of a transaction as recorded by this feature.


Comments, changes, additions?

Jan

--
#==================­====================­====================­============#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#==================­====================­============ JanWieck@Yahoo.com #

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 22 answer | Add comment
Re: Proposal: Change of pg_trigger.tg_enabl­ed and adding Jan Wieck 16:27:00
 On 1/25/2007 6:55 PM, Tom Lane wrote:> Jan Wieck <JanWieck@Yahoo.com­> writes:>> The value definitions of tg_enabled would be>
A fires always>> N fires never>> O fires on transaction origin only>> R fires on replica only>
A new per session GUC variable, restricted to superusers, will define if >> the session is in origin or replica mode.>
Are you sure two states are enough?

Good question. I don't know. I'd rather error on the safe side and make
it multiple states, for now I only have Normal and Replica mode.
No particular objection, but now would be the time to think if a boolean> is sufficient.>
Likewise the system catalog pg_rewrite is extended with an attribute >> ev_enabled. It will have the same possible values and a new command, >
I assume there'd be no intention of supporting on-the-fly changes of> this setting (ie, you'd set the GUC variable once at session startup> and not change thereafter)? Otherwise you'd have a problem with cached> plans.

This is indeed the intended use pattern. Since it is restricted to
superusers, I don't see a particular reason why to enforce it in the
system though.


Jan

--
#==================­====================­====================­============#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#==================­====================­============ JanWieck@Yahoo.com #

-------------------­--------(end of broadcast)---------­------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

comment 11 answers | Add comment
autovacuum process handling Alvaro Herrera 16:13:21
 Hi,

This is how I think autovacuum should change with an eye towards being
able to run multiple vacuums simultaneously:

1. There will be two kinds of processes, "autovacuum launcher" and
"autovacuum worker".

2. The launcher will be in charge of scheduling and will tell workers
what to do

3. The workers will be similar to what autovacuum does today: start when
somebody else tells it to start, process a single item (be it a table or
a database) and terminate

4. Launcher will be a continuously-runnin­g process, akin to bgwriter;
connected to shared memory

5. Workers will be direct postmaster children; so postmaster will get
SIGCHLD when worker dies

6. Launcher will start a worker using the following protocol:
- Set up information on what to run on shared memory
- invoke SendPostmasterSigna­l(PMSIGNAL_START_AUT­OVAC_WORKER)
- Postmaster will react by starting a worker, and registering it very
similarly to a regular backend, so it can be shut down easily when
appropriate.
(Thus launcher will not be informed right away when worker dies)
- Worker will examine shared memory to know what to do, clear the
request, and send a signal to Launcher
- Launcher wakes up and can start another one if appropriate

Does this raise some red flags? It seems straightforward enough to me;
I'll submit a patch implementing this, so that scheduling will continue
to be as it is today. Thus the scheduling discussions are being
deferred until they can be actually useful and implementable.

--
Alvaro Herrera http://www.CommandP­rompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 9 answers | Add comment
implementing (something like) UNIQUE constraint using PL/pgSQL Tomas Vondra 14:58:24
 Hello,

in our application we need to implement a constraint that enforces 'at
most N rows with this value', that is we have a table with 'flag' column
and for each value there should be at most 10 rows (for example, the
exact number does not matter).

I'm trying to implement a PL/pgSQL trigger to enforce this constraint,
but I'm not sure my solution is 'the best one possible'. The first
solution I came up with is this:

===================­====================­====================­==========

CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN

IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;

SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!'
END IF;

RETURN NEW;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);

===================­====================­====================­==========

But that obviously does not work as two sessions can reach the SELECT
INTO statement at the same time (or until one of them commits). Thus
there could be more than 'cnt' rows with the same value.

Then I used a 'FOR UPDATE' lock on a separate 'lock table' that already
contains all possible values of 'flag' (in reality the trigger tries to
insert that value and catches the 'duplicate key' exception but that's
not important here). The trigger is then

===================­====================­====================­==========

CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN

IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;

PERFORM flag FROM lock_table WHERE flag = NEW.flag FOR UPDATE;

SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!';
END IF;

RETURN NEW;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);

===================­====================­====================­==========

This works (af far as I know), but I'm not sure it's the best possible
solution - for example I'd like to remove the lock table. Is there some
'more elegant' way to do this?

Tomas

PS: Is there some up to date 'best practices' book related to PL/pgSQL?
All books I've found on Amazon are pretty old (about 5 years) or are
related to 'beginners' or different areas of development (PHP, etc.)

-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 2 answer | Add comment
Re: 10 weeks to feature freeze (Pending Work) Jonah H. Harris 12:31:25
 On 1/22/07, Joshua D. Drake <jd@commandprompt.c­om> wrote:> Jonah Harris: WITH/Recursive Queries?

Yup, just talked with Bruce about this last week. Working on the design now.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprised­b.com
Iselin, New Jersey 08830 | http://www.enterpri­sedb.com/

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 21 answer | Add comment
Speaking of upgrades... (was Re: Predicted ...) Ron Johnson 09:29:26
 -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/26/07 17:28, Shane Ambler wrote:> Bill Moran wrote:>> I spend some time googling this and searching the Postgresql.org site,>> but>> I'm either not good enough with the search strings, or it's not to be>> found.>>
I'm trying to plan upgrades so that we don't upgrade needlessly, but also>> don't get caught using stuff that nobody's supporting any more.>> The FreeBSD project keeps this schedule:>> http://www.freebsd.­org/security/#adv>> which is _really_ nice when talking to managers and similar people about>> when upgrades need to be scheduled.>>
Does the PostgreSQL project have any similar policy about EoLs? Even>> just>> a simple statement like, "it is our goal to support major branches for 2>> years after release" or some such?>>
There is no set time frame planned that I know of.>
It is more a matter of users that keep the old versions alive. Some with> large datasets on busy servers that can't allocate enough downtime to> upgrade tend to be keeping the older versions running.

How much does the on-disk structure of *existing* tables and indexes
change between x.y versions?

Between, for example, 8.0 and 8.2?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFupB8S9HxQb3­7XmcRArvWAKCwTj6kDG6­+rAa4vZ30PEQUkDHy5AC­g7CZf
8PaPJuy6gYBuCo5JNdx­gdBQ=
=olUx
-----END PGP SIGNATURE-----

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 7 answers | Add comment
PostgreSQL Data Loss BluDes 08:31:01
 Hi everyone,
I have a problem with one of my costomers.
I made a program that uses a PostgreSQL (win32) database to save its data.
My customer claims that he lost lots of data reguarding his own clients
and that those data had surely been saved on the database.
My first guess is that he is the one who deleted the data but wants to
blame someone else, obviously I can't prove it.

Could it be possible for PostgreSQL to lose its data? Maybe with a file
corruption? Could it be possible to restore these data?

My program does not modify or delete data since its more like a log that
only adds information. It is obviously possible to delete these logs but
it requires to answer "yes" to 2 different warnings, so the data can't
be deleted accidentally.

I have other customers with even 10 times the amount of data of the one
who claimed the loss but no problems with them.
He obviously made no backups (and claims whe never told him to do them
so we are responsible even for this) though the program has a dedicated
Backup-section.

Any suggestion?

Daniele

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 6 answers | Add comment
[pgsql-patches] Autovacuum launcher patch Alvaro Herrera 08:14:31
 Hello,

This patch separates autovacuum in two families of processes: one is the
"launcher", in charge of examining statistics and deciding when to start
a worker. The other is the worker, which is started by the postmaster
under command of the launcher, and processes what the launcher tells it
to process (by way of setting info up in shared memory).

The postmaster treats workers as regular backends; they are listed in
the backend list, so when it wants to shut down, it'll send a SIGTERM
signal just like everyone else, meaning it'll Just Work(tm).

The launcher is a dummy process; it never connects to any database.
Right now, the scheduling is more or less the same as before: it'll only
start a single worker, which will process a whole database. Or rather,
all tables in it that are determined to need vacuuming, per the old
rules. Currently, the launcher first examines the last autovacuum time
to determine which database to vacuum; the worker then examines the
stats to determine which tables to vacuum. Eventually this will need to
be changed so that the launcher tells the worker exactly what table to
work on.

I've been wondering how to make the scheduling work in the future, when
we need to have the launcher read stuff from catalogs to configure the
scheduling ... Maybe the solution will be to store flatfiles based on
the catalogs, like we do for pg_database and pg_authid.

Comments are welcome.

--
Alvaro Herrera http://www.CommandP­rompt.com/
The PostgreSQL Company - Command Prompt, Inc.



-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate
comment 1 answer | Add comment
How does EXEC_BACKEND process signals? Alvaro Herrera 08:04:40
 In testing the new autovac facility, I noticed this log in the
EXEC_BACKEND (on Linux) scenario (I pressed Ctrl-C only once):

DEBUG: postmaster received signal 2
LOG: received fast shutdown request
LOG: aborting any active transactions
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
DEBUG: drop cascades to table fktable
DEBUG: drop auto-cascades to type fktable
DEBUG: drop cascades to table pktable
DEBUG: drop auto-cascades to type pktable
DEBUG: drop auto-cascades to constraint pktable_pkey on table pktable
DEBUG: drop auto-cascades to index pktable_pkey
DEBUG: server process (PID 21893) exited with exit code 1
DEBUG: server process (PID 21895) exited with exit code 1
DEBUG: server process (PID 21899) exited with exit code 1
DEBUG: server process (PID 21900) exited with exit code 1
DEBUG: server process (PID 21902) exited with exit code 1
DEBUG: server process (PID 21904) exited with exit code 1
DEBUG: server process (PID 21906) exited with exit code 1
LOG: shutting down
LOG: autovacuum launcher shutting down
DEBUG: forked new backend, pid=21907 socket=6
LOG: database system is shut down
LOG: background writer process (PID 21220) exited with exit code 0
LOG: terminating any other active server processes
DEBUG: sending SIGQUIT to process 21907
DEBUG: server process (PID 21907) exited with exit code 1
LOG: all server processes terminated; reinitializing
LOG: database system was shut down at 2007-01-26 20:21:10 CLST
LOG: checkpoint record is at 0/4293338
LOG: redo record is at 0/4293338; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 0/17395; next OID: 60723
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
DEBUG: transaction ID wrap limit is 2147484176, limited by database "postgres"
LOG: shutting down
DEBUG: recycled transaction log file "000000010000000000­000002"
DEBUG: recycled transaction log file "000000010000000000­000003"
LOG: database system is shut down

The strange thing is that we're seeing a "forked a new backend" line
_after_ the shutdown signal was received. I don't think this is related
to my local changes, because I've been careful with that, but one never
knows.

I wonder if this could cause more than just a curiosity. The backend
was evidently shut down promptly.

I'll post the autovac patch right away.

--
Alvaro Herrera http://www.CommandP­rompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 1 answer | Add comment
Synthesize support for Statement.getGenera­tedKeys()? Ken Johanson 07:57:59
 Hello,

I've just come to realize that Statement.getGenera­tedKeys() is not
supported in the current PG and/or JDBC driver. Does someone know if
this is a limitation of PG, or its protocol, or just not yet implemented
in the JDBC driver? I'm just wondering where, if at all (if I have
enough brain cells that is :)­, I could try to offer a patch or ideas..

Then question 2; I did see a discussion where it was suggested that we
could get roughly the same effect by issuing a
SELECT currval('<sequence-­name>'); after the DML...
http://archives.pos­tgresql.org/pgsql-jd­bc/2005-10/msg00035.­php
Would it then be feasible internal to the JDBC driver to (create an
option that would enable) always implicitly append that query to the
(String)sql arg of Statment.executeUpd­ate(String sql, String[]
columnNames)? I mean, just internally attempt to create the same
behavior as what this method should be doing?

Question 3 is, it seems like option two would only return the last
created key, not set of keys, in the case where multiple rows were
inserted.. is this accurate?

Unfortunately if I cant find a way to make my target-app work with PG
(without adding PG-specific modifications for getting keys), I'm
probably not going to be able to make the switch to PG unfortunately -
the code I'm working with makes really, really extensive use of
retrieved keys..

Thank you,
Ken



-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 28 answers | Add comment
Recursive query syntax ambiguity Gregory Stark 07:49:29
 
Hm, I had hoped that the DB2/ANSI syntax would only require making "WITH" a
fully reserved word, and not the other tokens it uses. Certainly for
non-recursive queries that's the case as the only other token it uses is "AS"
which is already a fully reserved word.

However to fully support the DB2/ANSI syntax we would definitely have an
ambiguity and I think we would have to make "CYCLE" a fully reserved word
which seems like a much bigger concession than "WITH". Observe the following
case:

WITH RECURSIVE foo (x,y) AS (select 1,2) SEARCH DEPTH FIRST BY x CYCLE x,y SET ...

The parser can't search arbitrarily far checking for a SET to see if the CYCLE
is a keyword or a binary operator. Even if it could things like this would be
entirely ambiguous:

WITH RECURSIVE foo (x,y) AS (select 1,2) SEARCH DEPTH FIRST BY x CYCLE x, y CYCLE y SET ...

I'm nowhere near actually implementing this functionality yet so there's no
pressing need for action. In fact I think the search clause is actually an
ANSIism that isn't supported by DB2 itself yet either.


--
Gregory Stark
EnterpriseDB http://www.enterpri­sedb.com

-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate

comment 8 answers | Add comment
Re: BUG #2917: spi_prepare doesn't accept typename Bruce Momjian 06:26:00
 
OK, what is the TODO wording?

-------------------­--------------------­--------------------­----------------

Andrew Dunstan wrote:> Jim Nasby wrote:> > On Jan 26, 2007, at 9:31 AM, Tom Lane wrote:> >> If you wanted to be a bit more ambitious maybe you could change the fact> >> that this code is throwing away typmod, which means that declarations> >> like "varchar(32)" would fail to work as expected. Perhaps it should be> >> fixed to save the typmods alongside the typioparams and then pass them> >> to InputFunctionCall instead of passing -1. On the other hand, we don't> >> currently enforce typmod for any function input or result arguments, so> >> maybe it's consistent that spi_prepare arguments ignore typmods too.> >> Thoughts?> >
I'd like to see us move towards supporting that; both for function > > parameters/results as well as inside functions. It'd be nice if both > > cases got fixed at once, but IMHO fixing only one now would be better > > than fixing none.> >
I'm not going to do either in fixing this bug - I think they should be > fixed but are a separate issue. These probably belong on the TODO list.>
cheers>
andrew>
-------------------­--------(end of broadcast)---------­------------------> TIP 3: Have you checked our extensive FAQ?>

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterpri­sedb.com

+ If your life is a hard drive, Christ can be your backup. +

-------------------­--------(end of broadcast)---------­------------------
TIP 2: Don't 'kill -9' the postmaster

comment 1 answer | Add comment

Add new topic:

How:  Register )
 
Login:   Password:   
Comments by: Premoderation:
Topic:
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или законы РФ. Ваш ip-адрес записывается.


QAIX > PostgreSQL database developmentGo to page: « previous | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | next »

see also:
[Installation & Configuration]…
[EJB/JBoss] - Re: Install Container…
[Performance Tuning] - NT Performance…
pass tests:
see also:
Mailing Lables

  Copyright © 2001—2010 QAIX
Идея: Монашёв Михаил.
Авторами текстов, изображений и видео, размещённых на этой странице, являются пользователи сайта.
See Help and FAQ in the community support.qaix.com.
Write in the community about the bugs you have noticedbugs.qaix.com.
Write your offers and comments in the communities suggest.qaix.com.
Information for parents.
Пишите нам на .
If you would like to report an abuse of our service, such as a spam message, please .
Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .