Re: [HACKERS] [NOVICE] Last ID Problem
Hi Tom and others, I think the correct solution is not to mess with what's admittedly a legacy aspect of our client API. Instead we should invent the INSERT RETURNING and UPDATE RETURNING commands that have been discussed repeatedly (see the pghackers archives). That would allow people to get what they want, and do so in only one network round trip, without any artificial dependencies on OIDs or TIDs or anything else. It'd be unportable, but surely no more so than relying on OIDs or TIDs ... Just off the top of my head, would it not be feasible to add a column to pg_class called lastinsert that points to the OID of the pg_attribute column to return after an insert? It could be changed using something similar to ALTER TABLE x SET LASTINSERT TO y, but by default it would be set to the OID of the primary key of the table if the table specified WITHOUT OIDS at creation time, or the first column of the table otherwise. After the INSERT command, the value of the resulting is column is passed back to the client. I see that INSERT...RETURNING is a solution to the problem, but it seems somewhat strange to have to use an unportable command just to be able to return an identifier for the last inserted record... Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Our getopt_long() doesn't do abbreviations or NLS
Tom Lane wrote: I seem to recall that there was some special consideration for files that would conditionally show up in multiple executables. Or were you going to fix that by having just one .mo file for all the clients? The current method is to explicitly register the source file in each catalog that might need it. This is not ideal, but there isn't a better way in the works at this time. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] libpq API incompatibility between 7.4 and 8.0
Martin Pitt has detected that the libpq API has changed incompatibly between 7.4 and 8.0. This has the effect, for example, that 7.4's psql cannot run with 8.0's libpq. Example: $ LD_LIBRARY_PATH=/home/peter/devel/pg80/pg-install/lib /home/peter/devel/pg74/pg-install/bin/psql --help /home/peter/devel/pg74/pg-install/bin/psql: relocation error: /home/peter/devel/pg74/pg-install/bin/psql: undefined symbol:get_progname I haven't looked further, but we have to fix this urgently, I think. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Problems with initdb 8.0.1
Hello
I have a problem running initdb 8.0.1. I get this error message when I
try to run this command in my system:
---
-bash-2.05b$ /local/opt/postgresql/bin/initdb
The program postgres is needed by initdb but was not found in the
same directory as
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL
PROTECTED].
Check your installation.
---
strace shows this:
---
..
..
getcwd(/, 1024) = 10
chdir(/local/opt/postgresql/bin) = 0
lstat64(initdb, {st_mode=S_IFLNK|0777, st_size=82, ...}) = 0
readlink(initdb,
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL
PROTECTED], 1024) = 82
chdir(/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin) =
0
lstat64([EMAIL PROTECTED], {st_mode=S_IFREG|0755, st_size=47506,
...}) = 0
getcwd(/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin,
1024) = 73
chdir(/) = 0
write(2, The program \postgres\ is needed..., 209The program
postgres is needed by initdb but was not found in the
same directory as
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL
PROTECTED].
Check your installation.
) = 209
exit_group(1) = ?
---
Some additional information:
We are running a system for administration of third party software on
UNIX computers (store). With this system we can compile in a master
server versions for different platforms and different versions of the
software for a platform, distribution happens automatic.
This system is well tested and works without a problem. We have been
running the last 8-9 version of postgres in this system without a
problem.
What the system does is to create a symblink to the version for your
machine (among other things).
For example:
If we install the binaries for postgres under /local/opt/postgresql/bin,
initdb in this directory will be a symblink to
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
and
/local/opt/postgresql/bin/postgres will we a symblink to
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
if we are in a linux machine.
If we run the same version in a solaris machine:
/local/opt/postgresql/bin/initdb --
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
and
/local/opt/postgresql/bin/postgres --
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
Solution to the problem?:
-
I solution to this problem will be to look for postgres under the same
directory of initdb (/local/opt/postgresql/bin in our example) and not
under the directory of the initdb symblink target.
Any comment to this?
Thanks for your time.
--
Rafael Martinez, [EMAIL PROTECTED]
Center for Information Technology Services
University of Oslo, Norway
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] FunctionCallN improvement.
Tom Lane wrote:
Based on this I think we ought to go with the unrolled approach, ie,
we'll create a macro to initialize the fixed fields of fcinfo but fill
in the arg and argisnull arrays with code like what's already in
FunctionCall2:
I agree. The unrolled approach is a good result in most environments.
I think that a new macro becomes the following:
#define InitFunctionCallInfoData(Fcinfo, Flinfo, Nargs) \
do {\
(Fcinfo)-flinfo = Flinfo; \
(Fcinfo)-context = NULL; \
(Fcinfo)-resultinfo = NULL;\
(Fcinfo)-isnull = false; \
(Fcinfo)-nargs = Nargs;\
} while(0)
I think that this macro is effective also in other function such as
ExecMakeFunctionResultNoSets. However, we should apply that after
actually examining the effect.
First of all, this macro will be applied only to fmgr.c, but I think
we better define it in fmgr.h.
regards,
---
A.Ogawa ( [EMAIL PROTECTED] )
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [NOVICE] Last ID Problem
Mark Cave-Ayland [EMAIL PROTECTED] writes: Just off the top of my head, would it not be feasible to add a column to pg_class called lastinsert that points to the OID of the pg_attribute column to return after an insert? No. The thing everyone is ignoring here is that the INSERT command tag format is not something we can just go and change. You certainly could not put anything in it that wasn't an integer, and I'm not sure it would even be safe to put a bigint. So most of the cases you might actually want (timestamp, bigserial, etc) would be ruled out. Hardly worth inventing such a feature. I see that INSERT...RETURNING is a solution to the problem, but it seems somewhat strange to have to use an unportable command just to be able to return an identifier for the last inserted record... How is what you're suggesting more portable? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problems with initdb 8.0.1
Rafael Martinez Guerrero [EMAIL PROTECTED] writes: If we install the binaries for postgres under /local/opt/postgresql/bin, initdb in this directory will be a symblink to /local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED] uxlibc63=20 and=20 /local/opt/postgresql/bin/postgres will we a symblink to /local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED] nuxlibc63. This naming convention is broken. Fix it by putting the platform info into the path instead, perhaps /local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/386linuxlibc63/bin/postgres The symlink following code is necessary to support relocatable installations, and we aren't going to change it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problems with initdb 8.0.1
Rafael Martinez Guerrero wrote:
Hello
I have a problem running initdb 8.0.1. I get this error message when I
try to run this command in my system:
---
-bash-2.05b$ /local/opt/postgresql/bin/initdb
The program postgres is needed by initdb but was not found in the
same directory as
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED].
Check your installation.
---
strace shows this:
---
..
..
getcwd(/, 1024) = 10
chdir(/local/opt/postgresql/bin) = 0
lstat64(initdb, {st_mode=S_IFLNK|0777, st_size=82, ...}) = 0
readlink(initdb,
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL
PROTECTED], 1024) = 82
chdir(/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin) =
0
lstat64([EMAIL PROTECTED], {st_mode=S_IFREG|0755, st_size=47506,
...}) = 0
getcwd(/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin,
1024) = 73
chdir(/) = 0
write(2, The program \postgres\ is needed..., 209The program
postgres is needed by initdb but was not found in the
same directory as
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL
PROTECTED].
Check your installation.
) = 209
exit_group(1) = ?
---
Some additional information:
We are running a system for administration of third party software on
UNIX computers (store). With this system we can compile in a master
server versions for different platforms and different versions of the
software for a platform, distribution happens automatic.
This system is well tested and works without a problem. We have been
running the last 8-9 version of postgres in this system without a
problem.
What the system does is to create a symblink to the version for your
machine (among other things).
For example:
If we install the binaries for postgres under /local/opt/postgresql/bin,
initdb in this directory will be a symblink to
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
and
/local/opt/postgresql/bin/postgres will we a symblink to
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
if we are in a linux machine.
If we run the same version in a solaris machine:
/local/opt/postgresql/bin/initdb --
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
and
/local/opt/postgresql/bin/postgres --
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]
Solution to the problem?:
-
I solution to this problem will be to look for postgres under the same
directory of initdb (/local/opt/postgresql/bin in our example) and not
under the directory of the initdb symblink target.
Any comment to this?
Thanks for your time.
What a very strange arrangement. Why not symlink the install dirs and
leave the executable names alone? Mangling the names of the executables
is surely a recipe for a hole in the foot - that you've got away with it
up to now seems more a matter of luck than good management ;-)
cheers
andrew
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Peter Eisentraut [EMAIL PROTECTED] writes: Martin Pitt has detected that the libpq API has changed incompatibly between 7.4 and 8.0. This has the effect, for example, that 7.4's psql cannot run with 8.0's libpq. [ shrug... ] I don't think we've ever guaranteed that anyway. I will resist putting get_progname back into libpq, because it should never have been there, at least not with that name: it's an undocumented infringement on application namespace. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? Well, the driver would be free to implement $sth-last_insert_id() using whatever proprietary extensions it has available. The non-portableness would at least be hidden in the driver layer. Switch out the driver and the right thing would happen. INSERT/UPDATE ... RETURNING isn't something a driver can take advantage of. It would require it to modify your statements which it can't do safely. So your application would have such non-portable SQL code written into it. Switch databases and your application code needs to be ported. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Martin Pitt [EMAIL PROTECTED] writes: What would you propose as a solution? Do nothing. The problem you are raising isn't very serious since RPM-style installations don't support concurrent installation of multiple PG versions anyway. That being the case, it doesn't really matter whether 8.0 psql can use a 7.4 library or vice versa. To do otherwise would essentially amount to deciding that get_progname is part of the exported API of libpq forevermore. That's not something I'm willing to buy into. It was a mistake that it was done that way in 7.4, and I want to rectify that mistake before it gets any more entrenched. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Bruce Momjian pgman@candle.pha.pa.us writes: Added to release checklist: * Update inet/cidr data types with newest Bind patches You should also add check for zic database updates. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Tom Lane wrote: Paul Vixie [EMAIL PROTECTED] writes: i have two suggestions. first, look at the rest of the current source file, in case there are other fixes. Right, I already grabbed the latest. second, track changes this source file during your release engineering process for each new pgsql version. Bruce, do you think this is worth adding to RELEASE_CHANGES? inet_net_ntop.c and inet_net_pton.c are both extracted from the BIND distribution. But they're hardly the only files we took from elsewhere. Yes, I do. Most of the stuff we pull from other OS projects has clearly-defined behavior, while inet/cidr seem to be still in flux a little. Added to release checklist: * Update inet/cidr data types with newest Bind patches -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.3.8 under FC3 takes excessive semaphores?
Mark Cave-Ayland wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 31 January 2005 16:35 To: Mark Cave-Ayland Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] 7.3.8 under FC3 takes excessive semaphores? (cut) Judging by the symptoms, you have built a version with what we now call --disable-spinlocks; that is, it didn't figure out how to do assembly TAS on your platform and fell back to using SysV semaphores for spinlocks. Quite aside from the drain on semaphores, the performance is going to be spectacularly bad, so you'd better fix that. regards, tom lane Hi Tom, I'd just about managed to come to the same conclusion before your email arrived by spending the afternoon with gdb tracing into the LWLock code :) I've just installed 7.4.6 on the same box and this time the spinlocks are correctly picked up and everything works as expected. I think rather than fixing the 7.3.x source, we'll take the plunge and plan for a complete dump and restore of the database cluster over a weekend. And with 7.4 and later we will throw a configure error if we don't find native spinlocks so you will have no surprises in the future. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [NOVICE] Last ID Problem
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? Well, the driver would be free to implement $sth-last_insert_id() using whatever proprietary extensions it has available. The non-portableness would at least be hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane [EMAIL PROTECTED] writes: INSERT/UPDATE ... RETURNING isn't something a driver can take advantage of. It would require it to modify your statements which it can't do safely. So your application would have such non-portable SQL code written into it. Switch databases and your application code needs to be ported. I really don't think it matters. Currently, in PostgreSQL, there is only 'one true way' to have a real unique identifier for any given tuple that is persistent across queries and this is a sequence. Since sequences are basically managed by the app, your driver (I'm assuming) can't reliably use them. This is kind of similar to the issues being talked about wrt user locks. Because the lack of a true persistent tuple identifier, they require some data to be passed to them from the app (not really a big deal for them, however). From the point of view of your driver, the real solution is to bump oid to 64 bits and un-deprecate it. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Enhancement suggestion
Allow ASC/DESC direction modifiers for index columns. Almost every database has this, and it is a pest to have to write a function for every index column you want descending. Now, it is not technically difficult (nothing more than compare(x,y) instead of compare(x,y) to produce the ordering) but it would be much better to simply add it to the language. It would obviously require at least two changes, one to the grammar and one to the indexing code.
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote: His point stands though: if you are accessing Postgres through some kind of connection-pooling software, currval() cannot be trusted across transaction boundaries, since the pool code might give your connection to someone else. In this situation the nextval-before-insert paradigm is the only way. I don't disagree with that; if the thread mentioned connection pooling then I must have overlooked it. (But in most of the applications I can think of, your uses of currval subsequent to an INSERT ought to be in the same transaction as the insert, so are perfectly safe. If your connection pooler takes control away from you within a transaction block, you need a less broken pooler...) That's the common situation I was talking about: doing an INSERT and immediately calling currval(), presumably in the same transaction. I should have been more clear about that and warned what could happen in other situations. Thanks. Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command that could be used to reset a connection between pooling assignments, so as to be sure that different pooled threads wouldn't see state that changes depending on what some other thread did. It seems like RESET CONNECTION ought to reset all currval() states to the error, currval not called yet condition. Comments? TODO update: * Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of all temporary tables, removal of any NOTIFYs, cursors, prepared queries(?), currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Enhancement suggestion
Dann Corbit [EMAIL PROTECTED] writes: Allow ASC/DESC direction modifiers for index columns. We aren't going to do that, because it would be a meaningless concept for indexes that don't impose a linear sort order (which is to say, everything except btrees). The concept that actually fits into PG's index structure is to offer reverse-sort-order btree operator classes. Providing these as standard equipment for all the built-in datatypes has been discussed several times --- I'm not sure if it's mentioned in the TODO file but probably it should be. In the meantime you can cons up your own reverse order opclass with little effort beyond writing the one comparison function wrapper. See the archives for details. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Added to release checklist: * Update inet/cidr data types with newest Bind patches You should also add check for zic database updates. Uh, we already have: * Update timezone data to match latest zic database (see src/timezone/README) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Tom Lane wrote: Martin Pitt [EMAIL PROTECTED] writes: What would you propose as a solution? Do nothing. The problem you are raising isn't very serious since RPM-style installations don't support concurrent installation of multiple PG versions anyway. That being the case, it doesn't really matter whether 8.0 psql can use a 7.4 library or vice versa. Partial upgrades is the key word. To do otherwise would essentially amount to deciding that get_progname is part of the exported API of libpq forevermore. That's not something I'm willing to buy into. It was a mistake that it was done that way in 7.4, and I want to rectify that mistake before it gets any more entrenched. We can rectify the mistake, but then we need to change the SONAME. That's what it's for. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Enhancement suggestion
Obviously, you cannot create ordering in hash indexes, which is why nobody else does that either. The list of relational database systems that offer asc/desc on btree index files is quite extensive. The list of relational database systems that do not offer it is: 1. PostgreSQL 2. ? It will make porting efforts more difficult and more confusing. If you have to write operators for dozens of index files it might scuttle the entire conversion project. Allowing custom operators allows exactly the same thing, I admit. But doing it a different way from everyone else is not a good idea. It seems trivial to do it. Every part of a CREATE INDEX statement is already non-standard to some extent because ANSI/ISO did not include it (a defect in my opinion). And every CREATE INDEX statement can look different than the others because the different index types have different purposes. I won't harp on it any more and if the core team is not interested that is not a problem with me. I can always do it myself if it annoys me enough. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 02, 2005 9:37 AM To: Dann Corbit Cc: pgsql-hackers Subject: Re: [HACKERS] Enhancement suggestion Dann Corbit [EMAIL PROTECTED] writes: Allow ASC/DESC direction modifiers for index columns. We aren't going to do that, because it would be a meaningless concept for indexes that don't impose a linear sort order (which is to say, everything except btrees). The concept that actually fits into PG's index structure is to offer reverse-sort-order btree operator classes. Providing these as standard equipment for all the built-in datatypes has been discussed several times --- I'm not sure if it's mentioned in the TODO file but probably it should be. In the meantime you can cons up your own reverse order opclass with little effort beyond writing the one comparison function wrapper. See the archives for details. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Peter Eisentraut [EMAIL PROTECTED] writes: We can rectify the mistake, but then we need to change the SONAME. That's what it's for. Well, if you just want to bump libpq's SO_MAJOR_VERSION, I won't object. This brings up a point that I think has been discussed before: we operate on the assumption that incrementing SO_MINOR_VERSION is enough to distinguish different releases of libpq, but in point of fact it is not. The Linux conventions for library names, for one, essentially require us to bump SO_MAJOR_VERSION for every release if we want to have any hope of letting different versions coexist. Perhaps our convention should be to bump SO_MAJOR_VERSION for each of our major releases and only use SO_MINOR_VERSION when we change a library in a dot-release. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: We can rectify the mistake, but then we need to change the SONAME. That's what it's for. Well, if you just want to bump libpq's SO_MAJOR_VERSION, I won't object. This brings up a point that I think has been discussed before: we operate on the assumption that incrementing SO_MINOR_VERSION is enough to distinguish different releases of libpq, but in point of fact it is not. The Linux conventions for library names, for one, essentially require us to bump SO_MAJOR_VERSION for every release if we want to have any hope of letting different versions coexist. Perhaps our convention should be to bump SO_MAJOR_VERSION for each of our major releases and only use SO_MINOR_VERSION when we change a library in a dot-release. According to our RELEASE_CHANGES documentation: The major version number should be updated whenever the source of the library changes to make it binary incompatible. Such changes include, but are not limited to: 1. Removing a public function or structure (or typedef, enum, ...) 2. Modifying a public functions arguments. 3. Removing a field from a public structure. so while I don't think we need to update the major number for every PostgreSQL major release, the removal of prog_name probably required a major bump. Oops. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian pgman@candle.pha.pa.us writes: According to our RELEASE_CHANGES documentation: The major version number should be updated whenever the source of the library changes to make it binary incompatible. Such changes include, but are not limited to: 1. Removing a public function or structure (or typedef, enum, ...) 2. Modifying a public functions arguments. 3. Removing a field from a public structure. so while I don't think we need to update the major number for every PostgreSQL major release, the removal of prog_name probably required a major bump. Well, the point is that get_progname *isn't* a public function. We never advertised it as a libpq entry point. What this really brings out to me is that our development process doesn't impose a very strong boundary between libpq and our bundled client programs. If the client programs were enforced to use only the documented public API of libpq then we'd not be having this discussion --- but stuff such as libpgport support functions tends to slip by under the radar. IIRC we've been bitten in exactly this way at least once before. What I'm suggesting is that we just solve the whole class of problems permanently, by abandoning the assumption that we're going to guarantee binary compatibility across major releases. I don't think that promise is really buying us anything very critical. If we don't go that way, then we need to have some automatic check that none of the client programs are using symbols they shouldn't be from libpq. (Hmm ... will the existence of the Windows port help here?) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: According to our RELEASE_CHANGES documentation: The major version number should be updated whenever the source of the library changes to make it binary incompatible. Such changes include, but are not limited to: 1. Removing a public function or structure (or typedef, enum, ...) 2. Modifying a public functions arguments. 3. Removing a field from a public structure. so while I don't think we need to update the major number for every PostgreSQL major release, the removal of prog_name probably required a major bump. Well, the point is that get_progname *isn't* a public function. We never advertised it as a libpq entry point. What this really brings out to me is that our development process doesn't impose a very strong boundary between libpq and our bundled client programs. If the client programs were enforced to use only the documented public API of libpq then we'd not be having this discussion --- but stuff such as libpgport support functions tends to slip by under the radar. IIRC we've been bitten in exactly this way at least once before. What I'm suggesting is that we just solve the whole class of problems permanently, by abandoning the assumption that we're going to guarantee binary compatibility across major releases. I don't think that promise is really buying us anything very critical. If we don't go that way, then we need to have some automatic check that none of the client programs are using symbols they shouldn't be from libpq. (Hmm ... will the existence of the Windows port help here?) Yes, I think Win32 will help as long as we don't let bad stuff get into libpqddll.def. The only downside I see to bumping the major number each time is that the major number could get pretty big. Do the dynamic library systems handle two-digit library version numbers properly? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Enhancement suggestion
On Wed, Feb 02, 2005 at 09:50:16AM -0800, Dann Corbit wrote: Obviously, you cannot create ordering in hash indexes, which is why nobody else does that either. The list of relational database systems that offer asc/desc on btree index files is quite extensive. How many in that list allow you to create custom operator classes? -- Alvaro Herrera ([EMAIL PROTECTED]) No deja de ser humillante para una persona de ingenio saber que no hay tonto que no le pueda enseñar algo. (Jean B. Say) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Connect By for 8.0
Christopher Kings-Lynne wrote: I notice the CONNECT BY patch has been updated for 8.0: http://gppl.moonbone.ru/ Seriously, we really need to get this into 8.1. Convert it to the standard WITH RECURSIVE syntax if necessary... Yep, we are just waiting for someone to do the work. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema
Tom, Why is that a problem? The complaint seems about analogous to saying we should not have groups because you can't REVOKE rights from an individual user if he has them via a group membership. Oh, mostly I'm just bitching because I had seeing a new feature I can't use ;-) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian wrote: The only downside I see to bumping the major number each time is that the major number could get pretty big. Do the dynamic library systems handle two-digit library version numbers properly? MySQL's client library is at 12, so I don't see a problem. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? Well, the driver would be free to implement $sth-last_insert_id() using whatever proprietary extensions it has available. The non-portableness would at least be hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. Well I'm not sure what you mean by portable. It's part of the DBI driver definition, so in theory it is. Not all drivers will implement it though, or implement it properly, and for some it may be more efficient than others. For postgres it looks like currently it requires you to pass in the table and field might even need a driver-specific hint telling it the sequence name. At least an application using it has a hope of working on a new driver. An application using RETURNING will only work on Oracle and one day Postgres. So it would be nice if the Postgres driver could efficiently implement it without having to do a second SELECT and without having to know out of band info like a sequence name. This is from the DBI documentation -- that is, the non-driver-specific abstract interface documentation. last_insert_id $rv = $dbh-last_insert_id($catalog, $schema, $table, $field); $rv = $dbh-last_insert_id($catalog, $schema, $table, $field, \%attr); Returns a value 'identifying' the row just inserted, if possible. Typically this would be a value assigned by the database server to a column with an auto_increment or serial type. Returns undef if the driver does not support the method or can't determine the value. The $catalog, $schema, $table, and $field parameters may be required for some drivers (see below). If you don't know the parameter values and your driver does not need them, then use undef for each. There are several caveats to be aware of with this method if you want to use it for portable applications: * For some drivers the value may only available immediately after the insert statement has executed (e.g., mysql, Informix). * For some drivers the $catalog, $schema, $table, and $field parameters are required (e.g., Pg), for others they are ignored (e.g., mysql). * Drivers may return an indeterminate value if no insert has been performed yet. * For some drivers the value may only be available if placeholders have not been used (e.g., Sybase, MS SQL). In this case the value returned would be from the last non-placeholder insert statement. * Some drivers may need driver-specific hints about how to get the value. For example, being told the name of the database 'sequence' object that holds the value. Any such hints are passed as driver-specific attributes in the \%attr parameter. * If the underlying database offers nothing better, then some drivers may attempt to implement this method by executing select max($field) from $table. Drivers using any approach like this should issue a warning if AutoCommit is true because it is generally unsafe - another process may have modified the table between your insert and the select. For situations where you know it is safe, such as when you have locked the table, you can silence the warning by passing Warn = 0 in \%attr. * If no insert has been performed yet, or the last insert failed, then the value is implementation defined. Given all the caveats above, it's clear that this method must be used with care. The last_insert_id method was added in DBI 1.38. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] pg_dump bug in 7.3.9 with sequences
Hello,
Ran into this little gem with a customer today:
This works:
create table foo (foo int not null, bar text);
create sequence foo_seq;
alter table foo alter column foo set default nextval('foo_seq');
pg_dump will correctly dump the table:
CREATE TABLE foo (
foo integer DEFAULT nextval('foo_seq'::text) NOT NULL,
bar text
);
--
-- TOC entry 3 (OID 107565218)
-- Name: foo_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE foo_seq
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;
However if you do the following:
create table foo (foo serial not null, bar text);
create sequence foo_seq;
alter table foo alter column foo set default nextval('foo_seq');
pg_dump will give you the following:
CREATE TABLE foo (
foo serial NOT NULL,
bar text
);
--
-- TOC entry 3 (OID 107566148)
-- Name: foo_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE foo_seq
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;
Which is wrong because we want the column foo to use a default of foo_seq
not foo_foo_seq.
Sincerely,
Joshua D. Drake
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? For postgres it looks like currently it requires you to pass in the table and field might even need a driver-specific hint telling it the sequence name. That is a shortcoming of the DBD::pg driver which really should be returning a key (comprised of columns, some or none of which may be defaulted by the server). The 'database supplied' integer assumption is bad, bad, bad. In fairness, getting the last returned key is a catastrophic limitation of sql that we must all work around (itself being a specific annoyance of that tricky devil, default columns). :-) The only thing that is going to meet your requirements is a system wide (well, at least table wide, but system wide would be better) 64 bit oid, which doesn't exist right now. Sequences (or more generally, defaulted columns) are application managed and difficult to deal with at the driver level. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: How is what you're suggesting more portable? Well, the driver would be free to implement $sth-last_insert_id() using whatever proprietary extensions it has available. The non-portableness would at least be hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. I'm not familiar with the Perl interface, but JDBC has a standardized interface for this: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int) http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys() I tend to agree that a protocol-level change is easier to support in a driver. If it's done by extending INSERT/UPDATE, the driver will need to parse and modify queries which is hairy at the best of times. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
Greg Stark [EMAIL PROTECTED] writes: This is from the DBI documentation -- that is, the non-driver-specific abstract interface documentation. Returns a value 'identifying' the row just inserted, if possible. Typically this would be a value assigned by the database server to a column with an auto_increment or serial type. Aside from the numerous serious problems pointed out in the documentation, this has an even more fatal objection, which is that it's unspecified what the result value is and thus there is no portable way of *using* the result after you have it. (If the PG driver returns an OID you certainly couldn't use that the same way as some other driver that returns a primary key ... especially a multicolumn primary key ...) This portable function is so unportable that I see no reason to accept it as precedent. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
Joshua D. Drake [EMAIL PROTECTED] writes:
However if you do the following:
create table foo (foo serial not null, bar text);
create sequence foo_seq;
alter table foo alter column foo set default nextval('foo_seq');
This is flat out pilot error: you do not get to mess with the default
expression of a SERIAL column, because it's part of the internal
implementation of the SERIAL pseudo-type. If I were going to do
anything about it, I'd patch ALTER TABLE to refuse the above command.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [NOVICE] Last ID Problem
This portable function is so unportable that I see no reason to accept it as precedent. Hm. Instead of altering the syntax, what slipping in the last inserted/updated tuple into the PQResult object? Maybe is a protocol level option? Now everybody gets to use it with minimal muss. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
create table foo (foo serial not null, bar text);
create sequence foo_seq;
alter table foo alter column foo set default nextval('foo_seq');
This is flat out pilot error: you do not get to mess with the default
expression of a SERIAL column, because it's part of the internal
implementation of the SERIAL pseudo-type. If I were going to do
anything about it, I'd patch ALTER TABLE to refuse the above command.
It is not pilot error if PostgreSQL allows it. There is
nothing illegal about the above commands in their execution.
The pg_dump application should recognize that the object has
changed and react accordingly.
Let me elaborate. Look at the following table (I didn't design it):
rp_nuke_old=# \d nuke_bbtopics
Table public.nuke_bbtopics
Column| Type | Modifiers
-++---
topic_id| integer| not null default
nextval('public.nuke_bbtopics_id_seq'::text)
forum_id| smallint | not null default '0'
topic_title | character(255) | not null default ''
topic_poster| integer| not null default '0'
topic_time | integer| not null default '0'
topic_views | integer| not null default '0'
topic_replies | integer| not null default '0'
topic_status| smallint | not null default '0'
topic_vote | smallint | not null default '0'
topic_type | smallint | not null default '0'
topic_last_post_id | integer| not null default '0'
topic_first_post_id | integer| not null default '0'
topic_moved_id | integer| not null default '0'
news_id | integer| not null default '0'
Indexes: nuke_bbtopics_pkey primary key btree (topic_id),
forum_id_nuke_bbtopics btree (forum_id),
nuke_bbtopics_news_id btree (news_id),
topic_last_post_id_nuke_bbtopics btree (topic_last_post_id),
topic_type_nuke_bbtopics btree (topic_type),
topic_vote_nuke_bbtopics btree (topic_vote)
Check constraints: $1 (forum_id = 0)
$2 (topic_views = 0)
$3 (topic_replies = 0)
$4 (topic_last_post_id = 0)
$5 (topic_first_post_id = 0)
$6 (topic_moved_id = 0)
Notice that topic_id is an integer with a default value of:
nextval('public.nuke_bbtopics_id_seq'::text) .
Now lets look at what pg_dump does to this table:
CREATE TABLE nuke_bbtopics (
topic_id serial NOT NULL,
forum_id smallint DEFAULT '0' NOT NULL,
topic_title character(255) DEFAULT '' NOT NULL,
topic_poster integer DEFAULT '0' NOT NULL,
topic_time integer DEFAULT '0' NOT NULL,
topic_views integer DEFAULT '0' NOT NULL,
topic_replies integer DEFAULT '0' NOT NULL,
topic_status smallint DEFAULT '0' NOT NULL,
topic_vote smallint DEFAULT '0' NOT NULL,
topic_type smallint DEFAULT '0' NOT NULL,
topic_last_post_id integer DEFAULT '0' NOT NULL,
topic_first_post_id integer DEFAULT '0' NOT NULL,
topic_moved_id integer DEFAULT '0' NOT NULL,
news_id integer DEFAULT '0' NOT NULL,
CONSTRAINT $1 CHECK ((forum_id = 0)),
CONSTRAINT $2 CHECK ((topic_views = 0)),
CONSTRAINT $3 CHECK ((topic_replies = 0)),
CONSTRAINT $4 CHECK ((topic_last_post_id = 0)),
CONSTRAINT $5 CHECK ((topic_first_post_id = 0)),
CONSTRAINT $6 CHECK ((topic_moved_id = 0))
);
Notice that pg_dump has changed the topic_id integer to the serial
psuedotype. Which when restored will create:
Table public.nuke_bbtopics
Column| Type |
Modifiers
-++-
topic_id| integer| not null default
nextval('public.nuke_bbtopics_topic_id_seq'::text)
forum_id| smallint | not null default '0'
topic_title | character(255) | not null default ''
topic_poster| integer| not null default '0'
topic_time | integer| not null default '0'
topic_views | integer| not null default '0'
topic_replies | integer| not null default '0'
topic_status| smallint | not null default '0'
topic_vote | smallint | not null default '0'
topic_type | smallint | not null default '0'
topic_last_post_id | integer| not null default '0'
topic_first_post_id | integer| not null default '0'
topic_moved_id | integer| not null default '0'
news_id | integer| not null default '0'
Check constraints: $1 (forum_id = 0)
$2 (topic_views = 0)
$3 (topic_replies = 0)
$4 (topic_last_post_id = 0)
$5 (topic_first_post_id = 0)
$6
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Tom Lane wrote: Well, if you just want to bump libpq's SO_MAJOR_VERSION, I won't object. Yes. Unless someone objects, I will do that for 8.0.* and 8.1.*. The Linux conventions for library names, for one, essentially require us to bump SO_MAJOR_VERSION for every release if we want to have any hope of letting different versions coexist. Perhaps our convention should be to bump SO_MAJOR_VERSION for each of our major releases and only use SO_MINOR_VERSION when we change a library in a dot-release. Well, the goal for coexistence is mainly the server and the server-dependent tools (pg_dump, psql, etc.). There is no per-se requirement to have libpq versions to coexist. If libpq has different sonames in different releases, then they would end up in differently named packages anyway (at least in Debian). If they don't have different sonames, then we declare that they are compatible, so it should be OK to have only the latest version installed. That requires us to stay honest with the sonames, but it does not require us to increase the sonames unnecessarily. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
On Wed, Feb 02, 2005 at 01:54:48PM -0800, Joshua D. Drake wrote: It is not pilot error if PostgreSQL allows it. There is nothing illegal about the above commands in their execution. The pg_dump application should recognize that the object has changed and react accordingly. ISTM this is a bug, but it's not clear to me what is the solution. I can think of two: 1. Changing the default is forbidden 2. When the default is changed, the dependency on the sequence is dropped, and the sequence itself is dropped. Which one do you think is best? Why? (I'd say less bad instead of best, but I'm not sure if that's a correct choice of words.) -- Alvaro Herrera ([EMAIL PROTECTED]) Someone said that it is at least an order of magnitude more work to do production software than a prototype. I think he is wrong by at least an order of magnitude. (Brian Kernighan) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
According to our RELEASE_CHANGES documentation: The major version number should be updated whenever the source of the library changes to make it binary incompatible. Such changes include, but are not limited to: 1. Removing a public function or structure (or typedef, enum, ...) 2. Modifying a public functions arguments. 3. Removing a field from a public structure. so while I don't think we need to update the major number for every PostgreSQL major release, the removal of prog_name probably required a major bump. Well, the point is that get_progname *isn't* a public function. We never advertised it as a libpq entry point. What this really brings out to me is that our development process doesn't impose a very strong boundary between libpq and our bundled client programs. If the client programs were enforced to use only the documented public API of libpq then we'd not be having this discussion --- but stuff such as libpgport support functions tends to slip by under the radar. IIRC we've been bitten in exactly this way at least once before. What I'm suggesting is that we just solve the whole class of problems permanently, by abandoning the assumption that we're going to guarantee binary compatibility across major releases. I don't think that promise is really buying us anything very critical. If we don't go that way, then we need to have some automatic check that none of the client programs are using symbols they shouldn't be from libpq. (Hmm ... will the existence of the Windows port help here?) Yes, it will. At least it will refuse to link with references that are not in the libpqdll.def file. It won't change if the *signature* of the functions change. flashback href=http://archives.postgresql.org/pgsql-hackers-win32/2004-10/msg0004 2.php ;-) + thread. //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] FunctionCallN improvement.
a_ogawa [EMAIL PROTECTED] writes: Tom Lane wrote: Based on this I think we ought to go with the unrolled approach, I agree. The unrolled approach is a good result in most environments. I have committed changes along this line in HEAD and 8_0 branches. First of all, this macro will be applied only to fmgr.c, but I think we better define it in fmgr.h. For the moment I just put it in fmgr.c to have a minimally invasive patch. We can make it globally available if there's evidence it's needed elsewhere. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Peter Eisentraut wrote: Tom Lane wrote: Well, if you just want to bump libpq's SO_MAJOR_VERSION, I won't object. Yes. Unless someone objects, I will do that for 8.0.* and 8.1.*. I am thinking we should up the 8.0.* and 8.1.* releases to have the same major number, but not make a major libpq bump for every major release unless it is required, like with prog_name. Is that the plan? I guess the big question is whether we would ever want a 7.2 psql or any other client to automatically use a 7.3 libpq. In fact by upping the major every time will 7.2 clients automatically use the 7.3 libpq or will they have to be relinked? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Peter Eisentraut [EMAIL PROTECTED] writes: ... If they don't have different sonames, then we declare that they are compatible, so it should be OK to have only the latest version installed. That requires us to stay honest with the sonames, but it does not require us to increase the sonames unnecessarily. Well, the problem I'm concerned about is how do we stay honest. We now realize that our devel procedures may not catch this sort of mistake. It might be that the Windows DLLs will catch any unintentional dependencies, but I'm not feeling especially comfortable about it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] unicode upper/lower functions
Hi list, Attached for your perusal, unicode versions of upper/lower, that work independent of locale except for the following languages: Turkish, Azeri, and Lithuanian. There are 15 locale specific cases in total not covered. -- John Hansen [EMAIL PROTECTED] GeekNET collate.tar.gz Description: application/compressed-tar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
Alvaro Herrera wrote: On Wed, Feb 02, 2005 at 01:54:48PM -0800, Joshua D. Drake wrote: It is not pilot error if PostgreSQL allows it. There is nothing illegal about the above commands in their execution. The pg_dump application should recognize that the object has changed and react accordingly. ISTM this is a bug, but it's not clear to me what is the solution. I can think of two: 1. Changing the default is forbidden 2. When the default is changed, the dependency on the sequence is dropped, and the sequence itself is dropped. 3. When the default is changed, the dependency is updated to reflect the new sequence. The old sequence is left intact as an independent object. Sincerely, Joshua D. Drake Which one do you think is best? Why? (I'd say less bad instead of best, but I'm not sure if that's a correct choice of words.) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [NOVICE] Last ID Problem
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Merlin Moncur wrote:
That is a shortcoming of the DBD::pg driver which really should be
returning a key (comprised of columns, some or none of which may be
defaulted by the server).
Actually, the spec comes from DBI, not DBD::Pg, and is inspired by
MySQL's last_insert_id function. It is a poorly-speced function,
but we've done our best in the upcoming version of DBD::Pg,
which will support it.
Greg Stark wrote:
For postgres it looks like currently it requires you to pass in
the table and field might even need a driver-specific hint telling
it the sequence name.
For the record, the only required field for DBD::Pg will be the table
name, although the name of the sequence is highly encouraged. Here's
the docs for the next version, the first which supports lii:
last_insert_id
$rv = $dbh-last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh-last_insert_id($catalog, $schema, $table, $field, \%attr);
Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always ignored.
The current value of the sequence is returned by a call to the
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.
If you do not know the name of the sequence, you can provide a table name and
DBD::Pg will attempt to return the correct value. To do this, there must be at
least one column in the table with a CNOT NULL constraint, that has a unique
constraint, and which uses a sequence as a default value. If more than one
column
meets these conditions, the primary key will be used. This involves some
looking up of things in the system table, so DBD::Pg will cache the sequence
name for susequent calls. If you need to disable this caching for some reason,
you can control it via the 'pg_cache' attribute.
Please keep in mind that this method is far from foolproof, so make your
script use it properly. Specifically, make sure that it is called
immediately after the insert, and that the insert does not add a value
to the column that is using the sequence as a default value.
Some examples:
$dbh-do(CREATE SEQUENCE lii_seq START 1);
$dbh-do(CREATE TABLE lii (
foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
baz VARCHAR));
$SQL = INSERT INTO lii(baz) VALUES (?);
$sth = $dbh-prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth-execute($_);
my $newid =
$dbh-last_insert_id(Cundef,undef,undef,undef,{sequence='lii_seq'});
print Last insert id was $newid\n;
}
If you did not want to worry about the sequence name:
$dbh-do(CREATE TABLE lii2 (
foobar SERIAL UNIQUE,
baz VARCHAR));
$SQL = INSERT INTO lii2(baz) VALUES (?);
$sth = $dbh-prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth-execute($_);
my $newid = $dbh-last_insert_id(undef,undef,lii2,undef);
print Last insert id was $newid\n;
}
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200502022110
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCAYgSvJuQZxSWSsgRAgg3AJ4id98pta0CQR2w3xgwkxnph7qW4wCeMAJH
g/eXhtcmvXei9mESDDXg/s8=
=QaUa
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always
ignored.
The current value of the sequence is returned by a call to the
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.
This suffers from the same problems that currval does when using
connection pools tho.
I previously suggested a function similar to last_insert_id in behaviour,
and have attached it to this email for reference.
Even so, this also suffers from the same problems when using a connection pool.
The solution I proposed, namely having the tuple returned by
inserts/updates (perhaps even deletes?) would only mean changing the
client library to handle this, and as an example, libpg could easily
figure out the OID of said tuple and return that if it's present for
PQExec() (for backwards compatibility just as it does today,) and add a
separate PQExecSelect() that instead returns the tuple(s) as if they had
been SELECTed.
--
John Hansen [EMAIL PROTECTED]
GeekNET
#include postgres.h
#include fmgr.h
#include storage/relfilenode.h
#include commands/sequence.h
static int64 _lastval = 0;
PG_FUNCTION_INFO_V1(nextval_new);
Datum nextval_new(PG_FUNCTION_ARGS) {
_lastval = DatumGetInt64(nextval(fcinfo));
PG_RETURN_INT64(_lastval);
}
PG_FUNCTION_INFO_V1(lastval);
Datum lastval(PG_FUNCTION_ARGS) {
PG_RETURN_INT64(_lastval);
}
SET search_path = pg_catalog;
BEGIN;
DELETE FROM pg_catalog.pg_proc WHERE proname = 'nextval';
CREATE FUNCTION nextval(text)
RETURNS bigint
AS 'lastval.so','nextval_new'
LANGUAGE 'C';
COMMENT ON FUNCTION nextval(text) IS 'sequence next value';
CREATE FUNCTION lastval()
RETURNS bigint
AS 'lastval.so','lastval'
LANGUAGE 'C';
COMMENT ON FUNCTION lastval() IS 'sequence last value';
COMMIT;
# -
lastval :
gcc -I /usr/include/postgresql/server/ -I /usr/include/postgresql/ -shared -o lastval.so lastval.c
strip lastval.so
install :
install -s -m 755 lastval.so $(DESTDIR)/usr/lib/postgresql/lib/;
clean :
rm -f *.o *~ core *.so;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: ... If they don't have different sonames, then we declare that they are compatible, so it should be OK to have only the latest version installed. That requires us to stay honest with the sonames, but it does not require us to increase the sonames unnecessarily. Well, the problem I'm concerned about is how do we stay honest. We now realize that our devel procedures may not catch this sort of mistake. It might be that the Windows DLLs will catch any unintentional dependencies, but I'm not feeling especially comfortable about it. I am hopefull Win32 will help here and am willing to give it another try. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian wrote: Peter Eisentraut wrote: Tom Lane wrote: Well, if you just want to bump libpq's SO_MAJOR_VERSION, I won't object. Yes. Unless someone objects, I will do that for 8.0.* and 8.1.*. I am thinking we should up the 8.0.* and 8.1.* releases to have the same major number, but not make a major libpq bump for every major release unless it is required, like with prog_name. Is that the plan? I guess the big question is whether we would ever want a 7.2 psql or any other client to automatically use a 7.3 libpq. In fact by upping the major every time will 7.2 clients automatically use the 7.3 libpq or will they have to be relinked? Uh, if we bump up the major library version in 8.0.X, will that require 8.0.0 user applications to be recompiled? That seems worse than having 7.4.X pg apps like psql fail. They fail because they are calling get_progname, but most user apps will not be calling that function. Also, if we bump the major version, will an install of 8.0.X fix problems with get_progname? Won't the 8.0.0 libpq with a higher minor version number still prevent old psql's from running? Our install doesn't uninstall old libraries, does it? RPM's might but I am not sure how they do minor upgrades. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian pgman@candle.pha.pa.us writes: In fact by upping the major every time will 7.2 clients automatically use the 7.3 libpq or will they have to be relinked? If you do not bump the soname then 7.2 clients will automatically immediately start using the new library when it's installed. (actually when ldconfig is run, which distributions will normally do automatically). If you do bump the versions then 7.2 clients will continue to use 7.2 libraries. If you have 7.2 clients, or even clients like DBD::Pg or other third-party libpq apps then you would have to relink them to use the new libraries. But you would probably just keep around both sets of libraries. I could see valid arguments for either when it comes to whether to bump it when it's not strictly necessary. If libpq were used by lots of external applications using the public interface then bumping it more than necessary means people have to keep around extra versions for no reason. On the other hand if libpq is seen as an integral part of the postgres package and its specific behaviour is important then not bumping the version forces admins to pick one version for everything when they might prefer that 7.2 clients stick with the 7.2 library. In a way the fact that you release new libpq versions when you release security releases for old releases like 7.3 or 7.4 sort of already implies an answer. If you thought the new library was plug-in compatible and should completely replace the old library automatically you wouldn't continue to release modified versions of the old library, you would just release HEAD for libpq all the time. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] subselects in the target list
This behavior seems inconsistent: neilc=# create table abc (a int, b int); CREATE TABLE neilc=# create function foo_abc() returns setof abc as 'select * from abc' language sql; CREATE FUNCTION neilc=# insert into abc values (5, 10); INSERT 17234 1 neilc=# insert into abc values (10, 20); INSERT 17235 1 neilc=# select a, foo_abc() from abc; a | foo_abc +- 5 | (5,10) 5 | (10,20) 10 | (5,10) 10 | (10,20) (4 rows) neilc=# select a, (select * from abc) from abc; ERROR: subquery must return only one column Is there a reason we can't treat a subselect in the target list as returning a composite type? For that matter, is this behavior also intentional? neilc=# create function foo_abc2() returns setof abc as 'declare row record; begin for row in select * from abc loop return next row; end loop; return; end' language plpgsql; CREATE FUNCTION neilc=# select a, foo_abc2() FROM abc; ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function foo_abc2 line 1 at return next -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] subselects in the target list
Neil Conway [EMAIL PROTECTED] writes: neilc=# select a, (select * from abc) from abc; ERROR: subquery must return only one column Is there a reason we can't treat a subselect in the target list as returning a composite type? Given the 8.0 infrastructure for unnamed record types it might be possible to do that; it was surely never possible before. Whether it's a good idea is another question. The syntax you are showing is designed to return a scalar. It will (and should) barf on multiple rows as well as multiple columns. For that matter, is this behavior also intentional? neilc=# select a, foo_abc2() FROM abc; ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function foo_abc2 line 1 at return next It's an implementation restriction in plpgsql: we didn't make it support the old-style SRF API. I'm unconvinced that it's worth fixing considering that this whole behavior (SRFs in the targetlist) is deprecated. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] subselects in the target list
On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: neilc=# select a, (select * from abc) from abc; ERROR: subquery must return only one column Is there a reason we can't treat a subselect in the target list as returning a composite type? Given the 8.0 infrastructure for unnamed record types it might be possible to do that; it was surely never possible before. Whether it's a good idea is another question. The syntax you are showing is designed to return a scalar. It will (and should) barf on multiple rows as well as multiple columns. Right, the point is, that is does not, if said srf-function is written in say, C. However, this is somewhat similar to the WITH LATERAL clause previously discussed in connection with UNNEST and multisets, so perhaps it's not such a bad idea after all? For that matter, is this behavior also intentional? neilc=# select a, foo_abc2() FROM abc; ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function foo_abc2 line 1 at return next It's an implementation restriction in plpgsql: we didn't make it support the old-style SRF API. I'm unconvinced that it's worth fixing considering that this whole behavior (SRFs in the targetlist) is deprecated. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- John Hansen [EMAIL PROTECTED] GeekNET ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
Joshua D. Drake [EMAIL PROTECTED] writes: Alvaro Herrera wrote: ISTM this is a bug, but it's not clear to me what is the solution. I can think of two: 1. Changing the default is forbidden 2. When the default is changed, the dependency on the sequence is dropped, and the sequence itself is dropped. 3. When the default is changed, the dependency is updated to reflect the new sequence. The old sequence is left intact as an independent object. What exactly is the use-case of that (or any other manipulation of a serial column's default)? There is no point that I can see in just rolling one sequence object into a serial in place of another. Whatever parameter change you might need to accomplish can be done with ALTER SEQUENCE on the original sequence, without replacing the object per se. (Except for renaming it; but given the way pg_dump handles this stuff, you do not actually have the option to control the sequence name anyway.) I also think that altering the default expression is useless --- it's not a serial column anymore if you do that. It might be worth trying to teach ALTER COLUMN TYPE to handle the cases of switching a serial column to a non-serial type or vice versa, but I don't think users should be allowed to reach in and mess with the default directly. In short I vote for #1. If you want to support #2 then teach ALTER COLUMN TYPE to handle it. #3 is simply pointless. BTW, experimenting with this reveals a different pg_dump issue, which is that it will not replicate a nondefault set of sequence parameters for a serial sequence. For instance dtest=# create table t1 (f1 serial); NOTICE: CREATE TABLE will create implicit sequence t1_f1_seq for serial column t1.f1 CREATE TABLE dtest=# alter sequence t1_f1_seq cycle; ALTER SEQUENCE pg_dump will just emit create table t1 (f1 serial) with no hint that the sequence ought to be set to CYCLE mode. I'm not sure about an appropriate fix offhand --- we can't very well use ALTER SEQUENCE in just this way in the dump, because of the risk of the sequence name being possibly different at reload. (Come to think of it, we are not very good about propagating GRANTs on the sequence either, because of the same risk.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] subselects in the target list
On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote: The syntax you are showing is designed to return a scalar. It will (and should) barf on multiple rows as well as multiple columns. I don't understand; the example I posted is of an SRF that returns multiple rows of multiple columns, which is transformed into multiple rows of a single column of composite type. I'm unconvinced that it's worth fixing considering that this whole behavior (SRFs in the targetlist) is deprecated. It is? I think if we polished it somewhat, this is reasonably cool functionality to have, and is consistent with the 8.0 work to make composite types more widely usable. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [NOVICE] Last ID Problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This suffers from the same problems that currval does when using connection pools tho. I still don't see this as much of a real world problem however, more of a doctor, it hurts when I do this variety. As the DBD::Pg docs point out, you should not separate the calls to insert and currval far apart, and certainly not across connections. I have a hard time visualizing a case where an app would ever need to worry about the problem anyway, unless they were using pooling in a strange way and had a very poorly written application. The solution I proposed, namely having the tuple returned by inserts/updates (perhaps even deletes?) would only mean changing the client library to handle this, and as an example, libpg could easily figure out the OID of said tuple and return that if it's present for PQExec() (for backwards compatibility just as it does today,) and add a separate PQExecSelect() that instead returns the tuple(s) as if they had been SELECTed. There's a few issues with the above, however, the most important of which is that OIDs are going away, and then what do you use? Also, it does not handle cases where the insert necessarily happened with a direct INSERT via PQexec: the insert could have happened inside of a called function, or a trigger could have inserted into three different tables. Truth be told, I don't think the whole last_insert_id() in DBI is a very useful function anyway. It's mainly (at this point) a quick abstraction of a nextval call between Oracle and PostgreSQL. We do go out of our way to be more compatible to MySQL by accepting just a table name, but one should really use the sequence directly, IMO. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502030012 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCAbQRvJuQZxSWSsgRArYMAKC4Kgsv153HHbC05AtraAh4O7oL9wCgtDmR zoucziPs5cyC1at00M8MC9w= =PDUD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] subselects in the target list
Neil Conway [EMAIL PROTECTED] writes: On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote: The syntax you are showing is designed to return a scalar. It will (and should) barf on multiple rows as well as multiple columns. I don't understand; the example I posted is of an SRF that returns multiple rows of multiple columns, which is transformed into multiple rows of a single column of composite type. I was speaking of the subselect syntax. I'm unconvinced that it's worth fixing considering that this whole behavior (SRFs in the targetlist) is deprecated. It is? I think if we polished it somewhat, this is reasonably cool functionality to have, and is consistent with the 8.0 work to make composite types more widely usable. What's deprecated is SETOF functions (ie, multiple return *rows*) in the targetlist. Although that may appear to work, it doesn't actually work very well --- in particular the behavior when you have more than one in the same targetlist isn't real sensible. So I'm not eager to see the subselect syntax extended to allow multiple rows to be returned, which is the other thing that this line of argument would lead to. I don't particularly care one way or the other about allowing a subselect to return a single row value; I'm just saying that reasoning from the behavior of SRFs isn't necessarily a good guide to what to do. There's some legacy behavior there. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Hi! (sorry for the additional addresses; I'm not subscribed to -hackers, so my mail will last a while until it arrives there). Tom Lane [2005-02-02 11:07 -0500]: Peter Eisentraut [EMAIL PROTECTED] writes: Martin Pitt has detected that the libpq API has changed incompatibly between 7.4 and 8.0. This has the effect, for example, that 7.4's psql cannot run with 8.0's libpq. [ shrug... ] I don't think we've ever guaranteed that anyway. If you don't guarantee a backwards compatible API, then you should give libpq a new SONAME. By keeping the SONAME 3 you did promise backwards compatibility. The problem with this issue is that it is a real pain for distributors to handle broken SONAMEs. If every distributor invents his own one, then there will be conflicts at some point. This gets worse if you do release a new libpq SONAME later, which might conflict with any fake SONAME a distributor might have invented. I will resist putting get_progname back into libpq, because it should never have been there, at least not with that name: it's an undocumented infringement on application namespace. I understand such concerns, but you cannot undo the history. libpq3 is out there and installed on maybe hundreds of thousands of machines, and your own psql frontend uses it. The next best option would be to fix the 7.4 version of psql to not use this symbol any more. However, this is still a pain since then you cannot upgrade from earlier versions to 8.0 any more. What would you propose as a solution? There can only be one /usr/lib/libpq.so.3. Thanks, Martin -- Martin Pitt http://www.piware.de Ubuntu Developerhttp://www.ubuntulinux.org Debian GNU/Linux Developer http://www.debian.org signature.asc Description: Digital signature
Re: [HACKERS] weird behaviour on DISTINCT ON
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |my warning was due the fact that in the docs is written nowhere this |drawback. | | | The SELECT reference page already says that the output rows are computed | before applying ORDER BY or DISTINCT. | | regards, tom lane True. I had to say my self: RTFM. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB/8fP7UpzwH2SGd4RAhpFAJ9x3jhMzJ3f94wnlN1DbxRNRQvOzACfXtVp +Zg1pVO7SsETwUx6fxCl7qw= =Q5EW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] unicode upper/lower functions
uhmm,... Forgot to change the copyright. Please accept this under the same terms as postgresql itself. ... John ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Crash when inserting gist records, or creating index on ( int, geom )
I tried asking on [EMAIL PROTECTED], and Mark
Cave-Ayland suggested
I ask you folks.
I installed PostGIS, which appeared to work just fine. The I
installed contrib/btree_gist, and since then I've had issues.
Some background: My postgres setup is on Red Hat linux, installed
from RPMs. It is a shared hosting environment, and postgres source
was not installed. So to build PostGIS , I checked the version
number, and downloaded the corresponding source.
cp_test=# select version();
version
-
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
I installed PostGIS 0.91:
cp_test=# select postgis_version();
postgis_version
---
0.9 USE_GEOS=0 USE_PROJ=0 USE_STATS=1
I ran postgis.sql without a problem. I also installed
contrib/btree_gist but I get a crash when creating a gist index:
cp_test=# create index trp_pool_index2 on trips using gist (
trp_matchpool, trp_org_geom gist_geometry_ops );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Also, there is now another way to crash it: the first update query
works, the second causes a crash:
cp_test=# Update trips set trp_org_geom=GeomFromText( 'POINT ('
||trp_org_lat||' '||trp_org_long ||')'::text , -1 ) where
tripid=355038;
UPDATE 1
cp_test=# Update trips set trp_org_geom=GeomFromText( 'POINT ('
||trp_org_lat||' '||trp_org_long ||')'::text , -1 ) where
tripid=355034;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
Note the record that causes the crash has currently an empty
trp_org_geom, the other does not:
cp_test=# select
tripid,trp_matchpool,trp_org_lat,trp_org_long,trp_org_geom,trp_dest_geom
from trips where tripid in (355038,355034);
tripid | trp_matchpool | trp_org_lat | trp_org_long |
trp_org_geom|trp_dest_geom
+---+-+--++-
355038 |-1 |43.55059 | -80.232725 |
SRID=-1;POINT(43.55059 -80.232725) | SRID=-1;POINT(43.769255
-79.409203)
355034 | -104 | 46.977101 | -70.552437 |
|
(2 rows)
My trips table is as follows:
Table public.trips
Column |Type | Modifiers
--+-+
tripid | integer | not null default
nextval('trips_tripid_seq'::text)
...
trp_matchpool| integer | not null default '-1'
...
trp_org_lat | double precision| not null default '0'
trp_org_long | double precision| not null default '0'
trp_dest_lat | double precision| not null default '0'
trp_dest_long| double precision| not null default '0'
...
trp_org_geom | geometry|
trp_dest_geom| geometry|
...
Indexes: trips_pkey primary key btree (tripid),
personid_trips_index btree (personid),
trp_loc_index gist (trp_org_geom, trp_dest_geom),
trp_org_index gist (trp_org_geom)
Check constraints: enforce_srid_trp_org_geom (srid(trp_org_geom) = -1)
enforce_geotype_trp_org_geom
((geometrytype(trp_org_geom) = 'POINT'::text) OR (trp_org_geom IS
NULL))
enforce_srid_trp_dest_geom (srid(trp_dest_geom) = -1)
enforce_geotype_trp_dest_geom
((geometrytype(trp_dest_geom) = 'POINT'::text) OR (trp_dest_geom IS
NULL))
Here is the bt from gdb:
(gdb) symbol-file /usr/bin/postgres
Reading symbols from /usr/bin/postgres...done.
(gdb) attach 13220
Attaching to Pid 13220
0x2ac26b22 in ?? ()
(gdb) cont
Continuing.
Program received signal SIGSEGV, Segmentation fault.
0x8082f0f in rt_box_union ()
(gdb) bt
#0 0x8082f0f in rt_box_union ()
#1 0x815fe3f in DirectFunctionCall2 ()
#2 0x2ae553af in ?? ()
#3 0x81604b5 in FunctionCall3 ()
#4 0x8071b7c in gistpenalty ()
#5 0x807112f in gistchoose ()
#6 0x806e574 in gistlayerinsert ()
#7 0x806e4c6 in gistdoinsert ()
#8 0x806e3d6 in gistinsert ()
#9 0x8160d7c in OidFunctionCall6 ()
#10 0x807b4d1 in index_insert ()
#11 0x80cfe5d in ExecInsertIndexTuples ()
#12 0x80cc6fc in ExecUpdate ()
#13 0x80cc2c3 in ExecutePlan ()
#14 0x80cb82d in ExecutorRun ()
#15 0x81175b2 in ProcessQuery ()
#16 0x8115c67 in pg_exec_query_string ()
#17 0x8116c09 in PostgresMain ()
#18 0x80ff013 in DoBackend ()
#19 0x80fe8b3 in
[HACKERS] fatal: cache id 30 (or alike)
Following belows thread http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg43381.html I am experiencing the same problem on a brand new, extremely fresh, solaris 5.9 and PostgreSQL 7.4.6 box. As expected, the problem was solved when passing the -d [1-5] to the postmaster. But... I was wondering (after googling and founding less than 1 page of posts related) if a diff was submited in order to avoid some strange debuging mesages when using the psql :) OUch...also using GIS, but I don't believe this has something to do. If needed, I would post the output messages, but thought that the above link will (exactly the same messages) provide an idea. Hints? Thanks in advance, Guido ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Hi Tom! Tom Lane [2005-02-02 12:01 -0500]: Martin Pitt [EMAIL PROTECTED] writes: What would you propose as a solution? Do nothing. That's unfortunately not an option. The problem you are raising isn't very serious since RPM-style installations don't support concurrent installation of multiple PG versions anyway. That being the case, it doesn't really matter whether 8.0 psql can use a 7.4 library or vice versa. That's exactly the point I want to change for Debian, I work on a structure which permits to run several clusters of different versions in parallel (see [1]). We now have a /usr/lib/libpq.so.3 for 7.4, and we can't have a _second_ incompatible /usr/lib/libpq.so.3 for 8.0. Sorry, but that's just the way how shared libraries work. SONAMEs are for API compatibility and it becomes _very_ hard to workaround broken ones. To do otherwise would essentially amount to deciding that get_progname is part of the exported API of libpq forevermore. That's not true. It must stay part of the exported API for SONAME 3 of libpq, not for anything else. That's not something I'm willing to buy into. It was a mistake that it was done that way in 7.4, and I want to rectify that mistake before it gets any more entrenched. Then please release 8.0.2 with a SONAME 4. Would you consider this? Thanks a lot and have a nice day! Martin [1] http://people.debian.org/~mpitt/postgresql-ng.html -- Martin Pitt http://www.piware.de Ubuntu Developerhttp://www.ubuntulinux.org Debian GNU/Linux Developer http://www.debian.org signature.asc Description: Digital signature
Re: [HACKERS] Crash when inserting gist records, or creating index on ( int, geom )
Robin Chauhan [EMAIL PROTECTED] writes: I installed PostGIS, which appeared to work just fine. The I installed contrib/btree_gist, and since then I've had issues. PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 Would you have any advice for me? Try a more modern Postgres. I note in the CVS logs bug fixes for NULLs in gist indexes as recently as 7.4.6. If you can reproduce this on 7.4.7 or 8.0 then I'd be interested to look at a test case ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] fatal: cache id 30 (or alike)
G u i d o B a r o s i o [EMAIL PROTECTED] writes: Following belows thread http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg43381.html I am experiencing the same problem on a brand new, extremely fresh, solaris 5.9 and PostgreSQL 7.4.6 box. As expected, the problem was solved when passing the -d [1-5] to the postmaster. But... I was wondering (after googling and founding less than 1 page of posts related) if a diff was submited in order to avoid some strange debuging mesages when using the psql :) It's fixed in 8.0, I believe. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
On Wed, Feb 02, 2005 at 03:49:59PM -0800, Joshua D. Drake wrote: Alvaro Herrera wrote: On Wed, Feb 02, 2005 at 01:54:48PM -0800, Joshua D. Drake wrote: It is not pilot error if PostgreSQL allows it. There is nothing illegal about the above commands in their execution. The pg_dump application should recognize that the object has changed and react accordingly. ISTM this is a bug, but it's not clear to me what is the solution. 3. When the default is changed, the dependency is updated to reflect the new sequence. The old sequence is left intact as an independent object. It seems reasonable to update the dependency. But it isn't reasonable to leave the old sequence intact, because it is an internal implementation detail that should not be left around. It would be a bug, because later when the table is dropped then you have a dangling object; this behavior would be equivalent to leaving the original sequence around when the table is dropped, which is exactly the scenario dependencies were written for. -- Alvaro Herrera ([EMAIL PROTECTED]) You liked Linux a lot when he was just the gawky kid from down the block mowing your lawn or shoveling the snow. But now that he wants to date your daughter, you're not so sure he measures up. (Larry Greenemeier) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
3. When the default is changed, the dependency is updated to reflect the new sequence. The old sequence is left intact as an independent object. What exactly is the use-case of that (or any other manipulation of a serial column's default)? There is no point that I can see in just rolling one sequence object into a serial in place of another. Whatever parameter change you might need to accomplish can be done with ALTER SEQUENCE on the original sequence, without replacing the object per se. (Except for renaming it; but given the way pg_dump handles this stuff, you do not actually have the option to control the sequence name anyway.) O.k. I will buy that. So I say: #3 rev2: When the default is changed, the dependency is updated to reflect the new sequence and the old sequence is dropped. I also think that altering the default expression is useless --- it's not a serial column anymore if you do that. It might be worth trying to teach ALTER COLUMN TYPE to handle the cases of switching a serial column to a non-serial type or vice versa, but I don't think users should be allowed to reach in and mess with the default directly. Well that would be fine if pg_dump actually handled the scenario I presented in my previous email correctly. The problem is you have situations where colummns became serial columns after the fact or they are columns that were created in a dataset before there was a serial data type (such as 7.2). Sincerely, Joshua D. Drake In short I vote for #1. If you want to support #2 then teach ALTER COLUMN TYPE to handle it. #3 is simply pointless. BTW, experimenting with this reveals a different pg_dump issue, which is that it will not replicate a nondefault set of sequence parameters for a serial sequence. For instance dtest=# create table t1 (f1 serial); NOTICE: CREATE TABLE will create implicit sequence t1_f1_seq for serial column t1.f1 CREATE TABLE dtest=# alter sequence t1_f1_seq cycle; ALTER SEQUENCE pg_dump will just emit create table t1 (f1 serial) with no hint that the sequence ought to be set to CYCLE mode. I'm not sure about an appropriate fix offhand --- we can't very well use ALTER SEQUENCE in just this way in the dump, because of the risk of the sequence name being possibly different at reload. (Come to think of it, we are not very good about propagating GRANTs on the sequence either, because of the same risk.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] libpq API incompatibility between 7.4 and 8.0
Bruce Momjian wrote: Uh, if we bump up the major library version in 8.0.X, will that require 8.0.0 user applications to be recompiled? No, they just keep using the old library. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] LWLock cache line alignment
Following some advice from Intel, http://www.intel.com/cd/ids/developer/asmo-na/eng/technologies/threading /20469.htm?page=2 I'm looking at whether the LWLock data structures may be within the same cache line. Intel uses 128 byte cache lines on its high end processors. slru.c uses BUFFERALIGN which is currently hardcoded in pg_config_manual.c to be #define ALIGNOF_BUFFER 32 which seems to be the wrong setting for the Intel CPUs, possibly others. In slru.c we have this code fragment: /* Release shared lock, grab per-buffer lock instead */ LWLockRelease(shared-ControlLock); LWLockAcquire(shared-buffer_locks[slotno], LW_EXCLUSIVE); The purpose of this is to reduce contention, by holding finer grained locks. ISTM what this does is drop one lock then take another lock by accessing an array (buffer_locks) which will be in the same cache line for all locks, then access the LWLock data structure, which again will be all within the same cache line. ISTM that we have fine grained LWLocks, but not fine grained cache lines. That means that all Clog and Subtrans locks would be effected, since we have 8 of each. For other global LWlocks, the same thing applies, so BufMgrLock and many other locks are effectively all the same from the cache's perspective. ...and BTW, what is MMCacheLock?? is that an attempt at padding already? It looks like padding out LWLock struct would ensure that each of those were in separate cache lines? Any views? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] LWLockRelease
A few thoughts on LWLock data structures... In lwlock.c we hold a list of lwlocks held: held_lwlocks[MAX_SIMUL_LWLOCKS] where #define MAX_SIMUL_LWLOCKS 100 The code for LWLockRelease assumes that the last acquired lock will always be the first one to be released, and uses an O(N) loop to search for the lock to release. Setting MAX_SIMUL_LWLOCKS to this fairly high number doesn't seem to match the optimistic use of the O(N) algorithm. Any thoughts on reducing the size of that array and/or reducing the lock release time? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
