Re: [BUGS] BUG #6480: NLS text width problem
Tom Lane t...@sss.pgh.pa.us writes: I think it'd be better to avoid depending on %*s for the data string and instead use it (with appropriate adjustment of the calculation) for the space-separator part of the format. Since that's a constant empty string, there shouldn't be any possibility of libc doing something other than what we intend. Sorry, I'm going on vacation for four days. Can't answer right now... -- Sergey Burladyan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Tom Lane t...@sss.pgh.pa.us writes: However, recordDependencyOnCurrentExtension doesn't know that the table is meant to be transient and links it to the current extension; so when the table gets dropped a bit later, the dependency code complains. [...] Instead, I'm tempted to propose that dependency.c explicitly allow drops of objects that belong to the current extension, when an extension is being created or updated. (That is, if we come across a dependency reference to the active extension, we just ignore it. A quick look suggests that this would require only a very small patch.) That would prevent the entire class of problems. Thinking about it, what I think makes sense at the user level is that you can either DROP an extension's object in the extension script or detach it so that it still exists on its own. That means we still need to be able to ALTER EXTENSION … DROP and that this operation should be automatically handled when the extension's script contains a DROP command. The way to implement that seems to be exactly what you're saying. (So that view is mostly useful for how to document the behaviour). It would also have the effect that explicit DROPs of member objects in extension scripts could be done without an explicit ALTER EXTENSION DROP first. I think we'd originally decided that requiring the ALTER was a good safety feature, but is it really more than nanny-ism? The intent of a DROP command seems pretty clear. What I remember we decided is that you can't DROP any single object of an extension alone, you have to drop the extension wholesale or not at all. So that you first “detach” the object from the extension then drop it. That makes perfect sense in general but is a useless restriction when executing an extension's script. I consider that bugfix for back branches, by the way (well, 9.1). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6334: initdb not working
Hi Alvaro, I just compiled version 9.1.3 and got quite the same error. I still do not have any clue what to change, as the compile runs successfully without any error. Regards Wilfried -Ursprüngliche Nachricht- Von: Alvaro Herrera [mailto:alvhe...@alvh.no-ip.org] Gesendet: Mittwoch, 11. Januar 2012 19:48 An: Pg Bugs Cc: Weiss, Wilfried Betreff: Fwd: AW: [BUGS] BUG #6334: initdb not working I'm forwarding a reply I got to my personal email. I have no idea what to do with this report. The interesting extract from the attached log file is: initializing dependencies ... ok creating system views ... FATAL: column ?column? specified more than once STATEMENT: /* * PostgreSQL System Views * * Copyright (c) 1996-2011, PostgreSQL Global Development Group * * src/backend/catalog/system_views.sql */ CREATE VIEW pg_roles AS Wilfried: please CC the list in replies always. --- Begin forwarded message from Weiss, Wilfried --- From: Weiss, Wilfried wilfried.we...@nsg.com To: Alvaro Herrera alvhe...@commandprompt.com Date: Wed, 11 Jan 2012 09:38:16 -0300 Subject: AW: [BUGS] BUG #6334: initdb not working Hi Alvaro, sorry for answering so late. I asked my AIX systems administrator for support on this issue. However they had no idea what kind of strace I could run to get more detailed information. Could you please give me any advice how to trace this bug? Attached you find the debugging output of initdb. This is the only information I have at the moment. Maybe this helps a little bit. Best Regards / Mit freundlichen Grüssen Wilfried Weiss A+W Basis Administration IS Group Services Pilkington Deutschland AG NSG Group Tel.: +49 (0)209 168 2624 Fax: +49 (0)209 168 2330 Mobil: +49 (0)171 306 9635 mailto:wilfried.we...@nsg.com -Ursprüngliche Nachricht- Von: Alvaro Herrera [mailto:alvhe...@commandprompt.com] Gesendet: Mittwoch, 14. Dezember 2011 23:06 An: Weiss, Wilfried Cc: Pg Bugs Betreff: Re: [BUGS] BUG #6334: initdb not working Excerpts from Wilfried.Weiss's message of mié dic 14 03:36:25 -0300 2011: The following bug has been logged on the website: Bug reference: 6334 Logged by: Wilfried Weiss Email address: wilfried.we...@nsg.com PostgreSQL version: 9.1.2 Operating system: AIX 5300-12-04-1119 Description: I successfully compiled 9.1.2 using IBM xlc 9.0 and gnu make 3.80-1. When trying to initialise the database, no directory structure is created. I used parameter -d for more debug info but did not find any useful hint why initdb is not working. I am using PostgreSQL since Version 7.0.4, but that never happened to me. With the amount of info in this report, it seems quite unlikely that we would be able to come up with an explanation. Maybe the operating system is silently failing the mkdir() calls. Would you run it under some sort of strace equivalent? --- End forwarded message --- -- Álvaro Herrera alvhe...@alvh.no-ip.org http://nsg.com/disclaimer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6523: Problems with dbpool()
The following bug has been logged on the website: Bug reference: 6523 Logged by: pradeep Email address: pradeep.vishwaka...@routesms.com PostgreSQL version: 9.1.2 Operating system: Window xp Description: Problem with COPY COMMAND in java code. File path can't read with dbpool.It require base connection(PostgreSql). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6523: Problems with dbpool()
pradeep.vishwaka...@routesms.com wrote: Problem with COPY COMMAND in java code. File path can't read with dbpool.It require base connection(PostgreSql). It sounds like you need to investigate whether dbpool includes support for database-specific extensions like COPY; and if so whether and how the PostgreSQL COPY command is supported. It looks like the website for dbpool is here: http://www.snaq.net/java/DBPool/ -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: It would also have the effect that explicit DROPs of member objects in extension scripts could be done without an explicit ALTER EXTENSION DROP first. I think we'd originally decided that requiring the ALTER was a good safety feature, but is it really more than nanny-ism? The intent of a DROP command seems pretty clear. What I remember we decided is that you can't DROP any single object of an extension alone, you have to drop the extension wholesale or not at all. So that you first âdetachâ the object from the extension then drop it. That makes perfect sense in general but is a useless restriction when executing an extension's script. Actually, on further thought I am not sure we really considered the idea of a DROP in an extension script at all --- it's not something that you would normally expect an extension to want to do for an exported object, and I'm pretty sure none of us thought about transient objects. But anyway, we all seem to agree that this seems like a reasonable fix, so I will look into making it happen. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell mike.blackw...@rrd.com wrote: works for me -- what version are you on? merlin -- [wcs1459@aclnx-cisp01 ~]$ psql --version psql (PostgreSQL) 9.1.1 contains support for command-line editing [wcs1459@aclnx-cisp01 ~]$ cat x create table a ( id serial, stuff text, more_stuff text ); create table a_audit ( id serial, a_old a, a_new a ); alter table a add column even_more_stuff boolean not null default false; [wcs1459@aclnx-cisp01 ~]$ psql -f x psql:x:5: NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id CREATE TABLE psql:x:11: NOTICE: CREATE TABLE will create implicit sequence a_audit_id_seq for serial column a_audit.id CREATE TABLE psql:x:13: ERROR: cannot alter table a because column a_audit.a_new uses its row type aha! that's not what you posted last time. you appended 'not null default false'; which inexplicably breaks the ALTER. try this: ALTER TABLE a ADD COLUMN even_more_stuff text not null; ALTER TABLE a ALTER even_more_stuff set default false; ALTER TABLE a DROP COLUMN even_more_stuff; ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false; (this really looks like a bug in postgres, cc-ing to bugs) merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
I wrote: But anyway, we all seem to agree that this seems like a reasonable fix, so I will look into making it happen. The attached proposed patch fixes the symptom Phil reported. However, I think we still have some work to do. I experimented with creating temp tables within an extension upgrade script, and found two interesting misbehaviors that the patch doesn't fix: 1. If you forget to drop the temp table before ending the script, then when the session ends and the temp table is forcibly dropped, the whole extension goes away (following the rule that a forced drop of an extension member makes the whole extension go away). This is mildly annoying, but since not dropping the temp table is a clear bug in an extension script, I think we can live with it. 2. #1 applies only in the typical case where the backend's temp table schema already exists. Otherwise, when we create the pg_temp_N schema it gets listed as one of the extension's objects. Then, when you exit the session, this happens (behind the scenes; it's logged in the postmaster log but not shown to the client): FATAL: cannot drop schema pg_temp_2 because extension myext requires it HINT: You can drop extension myext instead. This is really bad: any temp tables created in this session won't be cleaned out. And the same for any temp tables created in future sessions using the same backend ID slot, since they'll get the identical error on exit. Even worse, if you decide to drop the extension, you might be taking out temp tables that belong to some active session other than your own. Given those hazards and the fact that there's no reasonable way for an extension script to avoid the risk, I think this one is a must-fix. I don't see any easy way around this one other than kluging temp-schema creation to not link the temp schema to the active extension. Which is exactly what I'd not wanted to do in ATRewriteTable. The one bright spot about it is that temp-table schemas are inherently a special case because of their weird creation process, so we could have some comfort that there are probably not other similar bugs lurking. Thoughts, better ideas? regards, tom lane diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index db86262b4f06ec5b78f834eb10fbae45a1e77033..eca064f0cdef7ee6367b76a4da785e29a9cfdbc6 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *** findDependentObjects(const ObjectAddress *** 560,576 * another object, or is part of the extension that is the * other object. We have three cases: * ! * 1. At the outermost recursion level, disallow the DROP. (We ! * just ereport here, rather than proceeding, since no other ! * dependencies are likely to be interesting.) However, if ! * the owning object is listed in pendingObjects, just release ! * the caller's lock and return; we'll eventually complete the ! * DROP when we reach that entry in the pending list. */ if (stack == NULL) { char *otherObjDesc; if (pendingObjects object_address_present(otherObject, pendingObjects)) { --- 560,580 * another object, or is part of the extension that is the * other object. We have three cases: * ! * 1. At the outermost recursion level, we normally disallow ! * the DROP. (We just ereport here, rather than proceeding, ! * since no other dependencies are likely to be interesting.) ! * However, there are exceptions. */ if (stack == NULL) { char *otherObjDesc; + /* + * Exception 1a: if the owning object is listed in + * pendingObjects, just release the caller's lock and + * return. We'll eventually complete the DROP when we + * reach that entry in the pending list. + */ if (pendingObjects object_address_present(otherObject, pendingObjects)) { *** findDependentObjects(const ObjectAddress *** 579,584 --- 583,603 ReleaseDeletionLock(object); return; } + + /* + * Exception 1b: if the owning object is the extension + * currently being created/altered, it's okay to continue + * with the deletion. This allows dropping of an + * extension's objects within the extension's scripts, + * as well as corner cases such as dropping a transient + * object created within such a script. + */ + if (creating_extension + otherObject.classId == ExtensionRelationId + otherObject.objectId == CurrentExtensionObject) + break; + + /* No exception applies, so throw the error */ otherObjDesc = getObjectDescription(otherObject); ereport(ERROR, (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST), -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your
Re: [BUGS] Extension tracking temp table and causing update failure
On Wed, Mar 7, 2012 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: The attached proposed patch fixes the symptom Phil reported. However, I think we still have some work to do. I experimented with creating temp tables within an extension upgrade script, and found two interesting misbehaviors that the patch doesn't fix: 1. If you forget to drop the temp table before ending the script, then when the session ends and the temp table is forcibly dropped, the whole extension goes away (following the rule that a forced drop of an extension member makes the whole extension go away). This is mildly annoying, but since not dropping the temp table is a clear bug in an extension script, I think we can live with it. This seems a little bit more than mildly annoying to me. In the CREATE TABLE docs it reads: Temporary tables are automatically dropped at the end of a session... On a cursory read through those docs and also section 35.15 on extensions I see no mention of temp tables not being dropped as a bug. It seems like it would be an intuitive thing for people to assume that they wouldn't need to drop them inside of an extension. Also, if I am understanding this correctly, all objects that depend on said extension would also get dropped. So, for example, any tables that have a column with a data type from the extension would also be dropped. And if that table had foreign key references, all those tables would get dropped as well. So on and so forth, you get the idea. This seems like a pretty heavy penalty to pay for what seems like an easy mistake to make. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
On Wed, Mar 7, 2012 at 1:17 PM, Mike Blackwell mike.blackw...@rrd.com wrote: As a followup, the workaround fails if there is data in the source table due to the initial null value placed in the existing data rows. [wcs1459@aclnx-cisp01 ~]$ psql --port=5433 -e -f x begin; BEGIN create table a ( id serial, stuff text, more_stuff text ); psql:x:6: NOTICE: CREATE TABLE will create implicit sequence a_id_seq for ser ial column a.id CREATE TABLE create table a_audit ( id serial, a_old a, a_new a ); psql:x:12: NOTICE: CREATE TABLE will create implicit sequence a_audit_id_seq for serial column a_audit.id CREATE TABLE insert into a (stuff, more_stuff) values ('some', 'thing'); INSERT 0 1 ALTER TABLE a ADD COLUMN even_more_stuff boolean not null; psql:x:17: ERROR: column even_more_stuff contains null values ALTER TABLE a ALTER even_more_stuff set default false; psql:x:18: ERROR: current transaction is aborted, commands ignored until end of transaction block ALTER TABLE a DROP COLUMN even_more_stuff; psql:x:19: ERROR: current transaction is aborted, commands ignored until end of transaction block ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false; psql:x:20: ERROR: current transaction is aborted, commands ignored until end of transaction block rollback; ROLLBACK yup (please respond to the list) -- you can workaround the workaround by UPDATEing the table to set the field before applying the not null bit. Note that if you did this, the foreign table containing the type would have the new column all as null. IMO, the server is being too strict on the dependency check. Perhaps there are some defenses here that are an early form of trying to get field constraints to pass through to the foreign column, or it's just a plain old bug. I took a quick look at tablecmds.c to see if I could find an easy fix, but it wasn't clear why the default was forcing an dependency error and I punted. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
Merlin Moncure mmonc...@gmail.com writes: On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell mike.blackw...@rrd.com wrote: alter table a add column even_more_stuff boolean not null default false; aha! that's not what you posted last time. you appended 'not null default false'; which inexplicably breaks the ALTER. try this: ALTER TABLE a ADD COLUMN even_more_stuff text not null; ALTER TABLE a ALTER even_more_stuff set default false; ALTER TABLE a DROP COLUMN even_more_stuff; ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false; (this really looks like a bug in postgres, cc-ing to bugs) It is not a bug. The ALTER ADD ... DEFAULT ... form implies rewriting every existing tuple of the rowtype to insert a non-null value in the added column, and we don't have support for doing that to rowtype columns, only to the target table and descendants. Without a default, it's just a catalog adjustment and doesn't involve rewriting any data. (This stems from the fact that columns beyond a tuple's natts value are presumed null, so we can let ADD COLUMN without a default just change the catalogs and a null column effectively springs into existence for every existing tuple. ALTER ADD ... DEFAULT is specified to have a different result, and it's not free.) This probably could be done for rowtype columns as well, but nobody has collected the necessary round tuits. I think there was some fear of locking/deadlock issues, too. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
Excerpts from Tom Lane's message of mié mar 07 17:31:32 -0300 2012: This probably could be done for rowtype columns as well, but nobody has collected the necessary round tuits. I think there was some fear of locking/deadlock issues, too. It's probably easy to do if you require it to be marked INVALID initially and then validate the tables using it one by one. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
On Wed, Mar 7, 2012 at 2:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell mike.blackw...@rrd.com wrote: alter table a add column even_more_stuff boolean not null default false; aha! that's not what you posted last time. you appended 'not null default false'; which inexplicably breaks the ALTER. try this: ALTER TABLE a ADD COLUMN even_more_stuff text not null; ALTER TABLE a ALTER even_more_stuff set default false; ALTER TABLE a DROP COLUMN even_more_stuff; ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false; (this really looks like a bug in postgres, cc-ing to bugs) It is not a bug. The ALTER ADD ... DEFAULT ... form implies rewriting every existing tuple of the rowtype to insert a non-null value in the added column, and we don't have support for doing that to rowtype columns, only to the target table and descendants. I'm not buying that..it implies no such thing. In particular, for table-as-rowtype columns, there's no way that I can see to have default values be generated. So why does it follow that the dependent table has to be rewritten? Column constraints are not enforced on the rowtype, so it follows that default shouldn't be either considering there's no way to get the default to fire. Composite type (or table based composite) defaults are applied to the composite as a whole, not to specific fields. On a practical level, the error blocks nothing -- you can bypass it trivially. It's just an annoyance that prevents things that users would like to be able to do with table row types. So I'd argue to remove the check, although I can kinda see the argument that it's not a bug unless the check was recently introduced so that it broke older code. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Phil Sorber p...@omniti.com writes: On Wed, Mar 7, 2012 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. If you forget to drop the temp table before ending the script, then when the session ends and the temp table is forcibly dropped, the whole extension goes away (following the rule that a forced drop of an extension member makes the whole extension go away). This is mildly annoying, but since not dropping the temp table is a clear bug in an extension script, I think we can live with it. This seems a little bit more than mildly annoying to me. Well, I didn't say it wasn't annoying, but it would only be catastrophic if applied to a production database; and it's certainly the sort of thing you'd notice while testing the extension script. Keep in mind that those scripts run as superuser, so they can already do arbitrary amounts of damage to your DB if inadequately tested. If there were a reasonably non-ugly way to deal with the case I'd be willing to put it in, but I don't see one. I think the best we can do is document it. The other case, which only triggers if the script is run in a previously virgin backend ID, scares me a lot more because it's the kind of corner case that could easily escape reasonable testing of an extension script. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6480: NLS text width problem
Sergey Burladyan eshkin...@gmail.com writes: Tom Lane t...@sss.pgh.pa.us writes: I think it'd be better to avoid depending on %*s for the data string and instead use it (with appropriate adjustment of the calculation) for the space-separator part of the format. Since that's a constant empty string, there shouldn't be any possibility of libc doing something other than what we intend. Sorry, I'm going on vacation for four days. Can't answer right now... Ah, nevermind --- I re-read the patch and realized that it was already doing exactly what I said. Committed, sorry for the noise. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs