Re: [HACKERS] Tuplestore should remember the memory context it's created in
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: But we don't use tuplesort to return tuples from functions, so it's not broken in a user-visible way. Unless you can think of another scenario like that. (1) create a cursor whose plan involves a sort that will spill to disk (2) enter subtransaction (3) fetch from cursor (causing the sort to actually happen) (4) leave subtransaction (5) fetch some more from cursor Too busy to develop a test case right now, but ISTM it ought to fail. That was exactly the case that we originally fixed, that caused this PL/pgSQL issue. It works because cursors run within the portal ResourceOwner. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backup history file should be replicated in Streaming Replication?
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I think we could get away without the backup history file altogether. Hmmm ... actually I was confusing these with timeline history files, which are definitely not something we can drop. You might be right that the backup history file could be part of WAL instead. On the other hand, it's quite comforting that the history file is plain ASCII and can be examined without any special tools. I'm -1 for removing it, even if we decide to duplicate the info in a WAL record. Ok. How about writing the history file in pg_stop_backup() for informational purposes only. Ie. never read it, but rely on the WAL records instead. I just realized that the current history file fails to recognize this scenario: 1. pg_start_backup() 2. cp -a $PGDATA data-backup 3. create data-backup/recovery.conf 4. postmaster -D data-backup That is, starting postmaster on a data directory, without ever calling pg_stop_backup(). Because pg_stop_backup() was not called, the history file is not there, and recovery won't complain about not reaching the safe starting point. That is of course a case of don't do that!, but perhaps we should refuse to start up if the backup history file is not found? At least in the WAL-based approach, I think we should refuse to start up if we don't see the pg_stop_backup WAL record. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: David E. Wheeler wrote: +1 if it prevents indexes from being named CONCURRENTLY. Yeah, if you really want to have an index named like that you can use double quotes. Seems a sensible compromise. Well, this will also break tables and columns named concurrently. I think the odds of it being a problem are small, but still it is a reserved word that shouldn't be reserved according to the SQL spec. I suppose we could fix this by specifying a precedence and then explicitly checking if you're trying to make an index named concurrently and fixing it up later. Not unlike how you suggested we avoid making WITH a reserved word with the comment that there was more than one way to skin a cat -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fdw validation function vs zero catalog id
Martin Pihlak wrote: I wrote: The validator is run for the generic options specified to CREATE/ALTER FDW, SERVER and USER MAPPING (+ possible future SQL/MED objects). In this case the catalog is always known. Also we can assume that the catalog is known, if a user runs the validator directly. So yes, AFAICS there is no case for the or zero. Updated patch attached. This now also removes the or zero note from the documentation and modifies postgresql_fdw_validator() to assume that a valid catalog oid is always passed. Committed. I don't foresee any scenario either where we wouldn't know the catalog ID. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] win32env.c bug with no msvcrt
There is code in src/port/win32env.c to update the environment within the msvcrt.dll runtime even when running in msvcr70.dll (visual C++ 2005). It updates that one, the local one *and* the system environment. However, in case there is no msvcrt.dll linked into the process, it will abort at that stage and *not* update the system or local environment. This happens during my tests on Visual Studio 2008, and it causes the regression tests to fail (since they set environment variables for subprocesses - thus needing the system env update) The attached patch changes this to just ignore failure to find msvcr.dll. It usually doesn't happen, because we almost always link with third party libs that bring it in, but it can happen. Also, since we *never* check the return code from putenv anyway, returning an error code makes no sense... Given that we've had zero reports of this on previous versions, I'm not planning to backpatch this. Comments? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ win32env.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] executor and slru dtrace probes
Greg Smith píše v út 15. 12. 2009 v 12:10 -0500: Please send that updated version, and let's keep working on this into the next CommitFest, where it will be in the front of the queue rather than how it ended up at the tail of this one just based on its submission date. You're not really getting a fair chunk of time here between your review and the end here because of problems lining up reviewer time, that shouldn't happen next time. Make sense. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Well, this will also break tables and columns named concurrently. I think the odds of it being a problem are small, but still it is a reserved word that shouldn't be reserved according to the SQL spec. I suppose we could fix this by specifying a precedence and then explicitly checking if you're trying to make an index named concurrently and fixing it up later. No, not really. Past the grammar there is no way to tell concurrently from concurrently, ie, if we did it like that then you couldn't even use double quotes to get around it. Don't overthink this: either we reserve the word or we don't put in the feature. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
I wrote: Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Well, this will also break tables and columns named concurrently. I think the odds of it being a problem are small, but still it is a reserved word that shouldn't be reserved according to the SQL spec. I suppose we could fix this by specifying a precedence and then explicitly checking if you're trying to make an index named concurrently and fixing it up later. No, not really. Past the grammar there is no way to tell concurrently from concurrently, ie, if we did it like that then you couldn't even use double quotes to get around it. Don't overthink this: either we reserve the word or we don't put in the feature. I haven't heard anyone speak against making CONCURRENTLY semi-reserved, so I'll go ahead and do it that way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Remove code that attempted to rename index columns to keep them
Tom Lane wrote: Log Message: --- Remove code that attempted to rename index columns to keep them in sync with their underlying table columns. That code was not bright enough to cope with collision situations (ie, new name conflicts with some other column of the index). Since there is no functional reason to do this at all, trying to upgrade the logic to be bulletproof doesn't seem worth the trouble. This change means that both the index name and the column names of an index are set when it's created, and won't be automatically changed when the underlying table columns are renamed. Neatnik DBAs are still free to rename them manually, of course. Does this mean this TODO items should be modified to remove the index name mention? Add ALTER TABLE RENAME CONSTRAINT, update index name also -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Remove code that attempted to rename index columns to keep them
Bruce Momjian br...@momjian.us writes: Does this mean this TODO items should be modified to remove the index name mention? Add ALTER TABLE RENAME CONSTRAINT, update index name also No, that has nothing to do with this. We already have ALTER INDEX renaming the associated constraint, so I'm not sure how important the TODO item really is. But if someone were to do it, we'd certainly want the other direction to keep them in sync too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 2:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, not really. Past the grammar there is no way to tell concurrently from concurrently, ie, if we did it like that then you couldn't even use double quotes to get around it. Don't overthink this: either we reserve the word or we don't put in the feature. Well still in the realm of overthinking Is there anything to be gained by having a class of reserved word which can be used for columns but not relations? I think most of the conflicts we worry about are with column names, not table names, and reserving names from use as index names isn't even a standards violation. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@mit.edu writes: ? Well still in the realm of overthinking Is there anything to be gained by having a class of reserved word which can be used for columns but not relations? If there were more than a single member of the class, I might think it was worth the trouble ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: ? Well still in the realm of overthinking Is there anything to be gained by having a class of reserved word which can be used for columns but not relations? If there were more than a single member of the class, I might think it was worth the trouble ... Er, that was kind of my question. I kind of have the impression that we've reserved things in the past that were somewhat close decisions but only because people could conceivably have had columns by those names but they would never have had tables or indexes by those names. A quick glace at the list shows relatively few that actually need to be barred from columns. I could easily see people wanting to use columns named LEFT and RIGHT or VERBOSE. I suppose they've been there for a long time already though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: If there were more than a single member of the class, I might think it was worth the trouble ... A quick glace at the list shows relatively few that actually need to be barred from columns. I could easily see people wanting to use columns named LEFT and RIGHT or VERBOSE. I suppose they've been there for a long time already though. Hm. I do not believe it'd work for LEFT/RIGHT because of join condition syntax, but it might for ANALYZE, FREEZE, and VERBOSE; which would actually amount to a pretty significant percentage of our totally-outside-any-spec reserved words. I'm still not really eager to introduce yet another category of keywords, but perhaps it is worth doing. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Rep - 2-phase backups and reducing time to full replication
On Dec 22, 2009, at 12:54 PM, Simon Riggs wrote: 9. Create a recovery command file in the standby server with parameters required for streaming replication. 7. (a) Make a base backup of minimal essential files from primary server, load this data onto the standby. 10. Start postgres in the standby server. It will start streaming replication. 7. (b) Continue with second phase of base backup, copying all remaining files, ending with pg_stop_backup() Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming replica super-simple. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
On Dec 19, 2009, at 9:52 PM, Robert Haas wrote: On Sat, Dec 19, 2009 at 10:46 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian wrote: Seems I need some help here. I'm willing to work on this --- it doesn't look particularly fun but we really need it. You don't know fun until you have tried to stack hack upon hack and still create a reliable migration system. :-( They say that people who love sausage and respect the law should never watch either one being made, and I have to say I'm coming to feel that way about in-place upgrade, too. Perhaps we should be ordering bacon instead of sausage... Is there some reason why OIDs were used for ENUM instead of a general sequence? Were we worried about people screwing with the sequence? A sequences would presumably eliminate all these issues. Even if we wanted to disallow user access to the sequence, having something that's not competing with all the other uses of OID would presumably make this a lot simpler. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: About Allow VIEW/RULE recompilation when the underlying tables change
On Dec 19, 2009, at 4:38 PM, Robert Haas wrote: On Sat, Dec 19, 2009 at 1:56 PM, Caleb Welton cwel...@greenplum.com wrote: I maintain that the approaches that inform the user that they have met that condition via ALTER statement failures (Postgres/DB2/Microsoft Bound Views) have certain advantages over databases that allow the update but may silently leave views in an usable state (Oracle, Terradata), in that at least the user Knows when they have to re-examine their views. Agreed. As far as I can tell there are three approaches that could be taken to help address this problem: 1) DB2 like approach - try to perform rewrites where able, but if the rewrite fails then the alter operation fails. Would allow simple edits such as ALTER TYPE that are only changes in typmod, or if done more ambitiously would allow numbers to be changed to other numbers. But as Robert says this quickly approaches the territory of black magic. And it can easily lead to silent breakage - e.g. if you change an integer column to text, the view's attempt to coerce the text back to integer will continue working as long as that coercion is valid for all the data the view examines, but you have to think the user had a reason for changing the type... Or we could simply disallow those types of cases. It's not optimal, but would still provide a lot of benefit in other cases. BTW, +1 on allowing something akin to SELECT * to propagate ADD COLUMN, though for compatibility we should use something other that SELECT *. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range types
On Dec 15, 2009, at 6:29 PM, Jeff Davis wrote: On Tue, 2009-12-15 at 18:06 -0600, decibel wrote: Now that varlena's don't have an enormous fixed overhead, perhaps it's worth looking at using them. Obviously some operations would be slower, but for your stated examples of auditing and history, I suspect that you're not going to notice the overhead that much. For most varvarlena types, you only get stuck with the full alignment burden if you get unlucky. In this case, we're moving from 16 bytes to 17, which really means 24 bytes with alignment. Try creating two tables: My thought was that many timestamps don't actually need 16 bytes. Jan 1, 2000 certainly doesn't. So if your dates are close to the PG epoch, you can get away with far fewer than 8 bytes, which means varlena would be a win. *does some math* Actually, we're kinda screwed with microsecond time. Neglecting leap years and what-not, I come up with 8 years as the most you can represent in 6 bytes. The good news is that 7 bytes gets you all the way to 2284 (with uS precision), so we're not actually hurting ourselves on storage until 4284 or so. Not everyone needs uS precision, so it might be worth looking at a varlena-based timestamp. I was actually thinking about storing something like an absolute time and then an interval. That might have been able to compact a lot more if you used some kind of modified varlena (you'd want to store how long both the absolute time and the interval were). But again, we're rather screwed if you use uS precision. 1 byte header + 7 bytes for absolute gets us +/- 2284 years from epoch, but 4 bytes for interval only gives us 4294 seconds at uS precision. Maybe still worth it for those hour-long meetings. But if you switch to second precision, things get a lot more interesting: 1 byte overhead + 3 bytes interval gives you 194 days. 4 bytes of 1 second absolute time gets you epoch +/- 136 years. That means you could represent an entire period in 8 bytes. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
... The idea I had was to create a global structure:
struct pg_migrator_oids {
Oid pg_type;
Oid pg_type_array;
...
}
This would initialize to zero as a global structure, and only
pg_migrator server-side functions set it.
I would prefer *not* to do that, as that makes the list of settable oids
far more public than I would like; also you are totally dependent on
pg_migrator and the backend to be in sync about the definition of that
struct, which is going to be problematic in alpha releases in
particular, since PG_VERSION isn't going to distinguish them.
What I had in mind was more like
static Oid next_pg_class_oid = InvalidOid;
void
set_next_pg_class_oid(Oid oid)
{
next_pg_class_oid = oid;
}
Good point about requiring a link to a symbol; a structure offset would
not link to anything and would silently fail.
Does exporting a function buy us anything vs. exporting a variable?
in each module that needs to be able to accept a next-oid setting,
and then the pg_migrator loadable module would expose SQL-callable
wrappers for these functions. That way, any inconsistency shows up as
a link error: function needed not present.
I will work on a patch to accomplish this, and have pg_migrator link in
the .so only if the new server is = 8.5, which allows a single
pg_migrator binary to work for migration to 8.4 and 8.5.
I have completed the attached patch which assigns oids for all pg_type
rows when pg_dump --binary-upgrade is used. This allows user-defined
arrays and composite types to be migrated cleanly. I tested a reload of
the regression database with --binary-upgrade and all the pg_type oids
were identical. The pg_migrator changes required to use this feature
are trivial.
The remaining issue is pg_enum oids. Because it will be difficult to
pass an arbitrary number of oids into the backend, the idea was to
assign each enum value separately. If we implement this TODO item:
Allow adding/renaming/removing enumerated values to an existing
enumerated data type
Particularly the adding part rather than the renaming/removing part,
pg_dump can create an enum type with one (or zero perhaps) enums, and
then use a pg_enum oid-setting function and then use ALTER TYPE ADD
ENUM to add each new value.
Comments?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/catalog/heap.c
===
RCS file: /cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.361
diff -c -c -r1.361 heap.c
*** src/backend/catalog/heap.c 7 Dec 2009 05:22:21 - 1.361
--- src/backend/catalog/heap.c 23 Dec 2009 18:48:11 -
***
*** 1001,1013
if (IsUnderPostmaster (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
relkind == RELKIND_COMPOSITE_TYPE))
! {
! /* OK, so pre-assign a type OID for the array type */
! Relation pg_type = heap_open(TypeRelationId, AccessShareLock);
!
! new_array_oid = GetNewOid(pg_type);
! heap_close(pg_type, AccessShareLock);
! }
/*
* Since defining a relation also defines a complex type, we add a new
--- 1001,1007
if (IsUnderPostmaster (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
relkind == RELKIND_COMPOSITE_TYPE))
! new_array_oid = AssignTypeArrayOid();
/*
* Since defining a relation also defines a complex type, we add a new
Index: src/backend/catalog/pg_type.c
===
RCS file: /cvsroot/pgsql/src/backend/catalog/pg_type.c,v
retrieving revision 1.127
diff -c -c -r1.127 pg_type.c
*** src/backend/catalog/pg_type.c 16 Aug 2009 18:14:34 - 1.127
--- src/backend/catalog/pg_type.c 23 Dec 2009 18:48:11 -
***
*** 32,37
--- 32,38
#include utils/rel.h
#include utils/syscache.h
+ Oid binary_upgrade_next_pg_type_oid = InvalidOid;
/*
* TypeShellMake
***
*** 119,124
--- 120,131
*/
tup = heap_form_tuple(tupDesc, values, nulls);
+ if (OidIsValid(binary_upgrade_next_pg_type_oid))
+ {
+ HeapTupleSetOid(tup, binary_upgrade_next_pg_type_oid);
+ binary_upgrade_next_pg_type_oid = InvalidOid;
+ }
+
/*
* insert the tuple in the relation and get the tuple's oid.
*/
***
*** 409,418
values,
nulls);
! /* Force the OID if requested by caller, else heap_insert does it */
if (OidIsValid(newTypeOid))
HeapTupleSetOid(tup, newTypeOid);
!
typeObjectId =
Re: [HACKERS] Removing pg_migrator limitations
decibel deci...@decibel.org writes: Is there some reason why OIDs were used for ENUM instead of a general sequence? Were we worried about people screwing with the sequence? No, we were worried about being able to do enum_out without outside information as to which enum type it is. If you don't mind doubling the on-disk size of enum values, we could store the enum type OID and a sequence number instead. A sequences would presumably eliminate all these issues. Even if we wanted to disallow user access to the sequence, having something that's not competing with all the other uses of OID would presumably make this a lot simpler. The fact that it's shared with other uses of OID is 100% not relevant. A counter shared across all enums would pose the same issues. The only way to simplify matters would be to have each enum have its own value numbering, which would mean you need outside information to identify the associated label. Even if there were a really solid argument for changing this decision, doing so would create on-disk compatibility problems that would be even harder for pg_migrator to fix than what we're discussing now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Bruce Momjian br...@momjian.us writes: The remaining issue is pg_enum oids. Because it will be difficult to pass an arbitrary number of oids into the backend, the idea was to assign each enum value separately. If we implement this TODO item: Allow adding/renaming/removing enumerated values to an existing enumerated data type The reason that isn't implemented is that it's *hard* --- in fact, it appears to be entirely impossible in the general case, unless you're willing to change existing values of the enum on-disk. I do not agree that it's a good plan to try to solve that as a prerequisite to making pg_migrator work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 7:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm still not really eager to introduce yet another category of keywords, but perhaps it is worth doing. Comments? If we consider that they can now be a problem in pl/pgsql, it might be a good idea to consider it for this release. (I'm thinking of http://archives.postgresql.org/message-id/17728.1261002...@sss.pgh.pa.us for instance) -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
On Wed, Dec 23, 2009 at 7:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: The remaining issue is pg_enum oids. Because it will be difficult to pass an arbitrary number of oids into the backend, the idea was to assign each enum value separately. If we implement this TODO item: Allow adding/renaming/removing enumerated values to an existing enumerated data type The reason that isn't implemented is that it's *hard* --- in fact, it appears to be entirely impossible in the general case, unless you're willing to change existing values of the enum on-disk. I do not agree that it's a good plan to try to solve that as a prerequisite to making pg_migrator work. Shouldn't adding new ones be easy? As long as we're willing to make it fail with an error if there's a conflict -- which is sufficient for pg_dump's needs. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] About the CREATE TABLE LIKE indexes vs constraints issue
In connection with the operator-exclusion patch, Brendan Jurd dire...@gmail.com wrote: * What to do about INCLUDING INDEXES EXCLUDING CONSTRAINTS -- Postgres gets this wrong for unique indexes currently. Should we persist with the existing behaviour or fix it as part of this patch? My personal feeling was +1 for fixing it in this patch. I'm not sure whether we came to a conclusion about this point. The current code behavior is that INCLUDING INDEXES copies both plain indexes and index-based constraints, while INCLUDING/EXCLUDING CONSTRAINTS controls whether CHECK constraints are copied. Brendan argues that this is wrong, and I agree that it's not what the syntax would appear to mean. I think the most natural reading of the syntax would be INCLUDING INDEXES means to include everything you made with CREATE INDEX syntax, while INCLUDING CONSTRAINTS means to include everything you made with CONSTRAINT syntax. However, it's unclear whether that's so much better or more useful as to justify a compatibility break. Arguably, lumping all indexes together is the most useful behavior in practice. Another spanner in the works is that foreign-key constraints aren't copied. Not to mention that simple NOT NULL constraints are always copied independently of these options. So the whole thing fails to satisfy the POLA by pretty much any standard. Do we want to try to clean this up, or should we leave it alone on backwards-compatibility grounds? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 7:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: The reason that isn't implemented is that it's *hard* --- in fact, it appears to be entirely impossible in the general case, unless you're willing to change existing values of the enum on-disk. Shouldn't adding new ones be easy? No, not if you care about where they end up in the type's sort ordering. In pg_migrator's case that's not an issue because it's going to force the OID numbering for each of the elements. However, an ADD ENUM VALUE option that *doesn't* use a predetermined OID is going to end up inserting the new value at a not-very-predictable place. I do not think we should expose a half-baked behavior like that as standard SQL syntax. If we're going to implement something whose ambitions only extend to satisfying pg_migrator's needs, then it should be a specialized pg_migrator function. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 1:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: If there were more than a single member of the class, I might think it was worth the trouble ... A quick glace at the list shows relatively few that actually need to be barred from columns. I could easily see people wanting to use columns named LEFT and RIGHT or VERBOSE. I suppose they've been there for a long time already though. Hm. I do not believe it'd work for LEFT/RIGHT because of join condition syntax, but it might for ANALYZE, FREEZE, and VERBOSE; which would actually amount to a pretty significant percentage of our totally-outside-any-spec reserved words. I'm still not really eager to introduce yet another category of keywords, but perhaps it is worth doing. Comments? I'm about to get on a plane, but just to make you hurl here's another half-assed approach. ...Robert gross.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@gmail.com writes: I'm about to get on a plane, but just to make you hurl here's another half-assed approach. Uh ... I don't see what that fixes? If CONCURRENTLY can be a column name this is still ambiguous. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Dec 23, 2009, at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'm about to get on a plane, but just to make you hurl here's another half-assed approach. Uh ... I don't see what that fixes? If CONCURRENTLY can be a column name this is still ambiguous. How? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@gmail.com writes: On Dec 23, 2009, at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Uh ... I don't see what that fixes? If CONCURRENTLY can be a column name this is still ambiguous. How? Because CONCURRENTLY can still be reduced as tricky_index_name, so it still doesn't know how to parse CREATE INDEX CONCURRENTLY ON ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] what about _PG_fini
Hello dear hackers, I wonder what is the future of _PG_fini, documentation say at [1]: Note that _PG_fini will only be called during an unload of the file, not during process termination. (Presently, unloads are disabled and will never occur, but this may change in the future.) Since 8.2 it is the same and no entry in the TODO list for that, also I didn't find mails about that in the pgsql-hackers ML archive. So: 1. do we want a _PG_fini which is call on server stop ? 2. what's actually the best way to execute some code when server stop, if one have ideas ... ? Regards, Cédric Villemain [1] http://www.postgresql.org/docs/8.4/static/xfunc-c.html#XFUNC-C-DYNLOAD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
On Wed, Dec 23, 2009 at 8:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: If we're going to implement something whose ambitions only extend to satisfying pg_migrator's needs, then it should be a specialized pg_migrator function. Fwiw my feeling was the opposite here. It's better to offer even limited SQL-level support for features pg_migrator needs because the more abstract and loosely coupled the interface is between pg_migrator and the internals the better. Even if the interface is somewhat limited and just good enough for pg_migrator's needs it's still easier to support a well-defined abstract interface than one that depends on knowing about the internal implementation. I can see I'm outvoted here though and you and Bruce are the ones writing the code so far... -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what about _PG_fini
=?ISO-8859-1?Q?C=E9dric_Villemain?= cedric.villemain.deb...@gmail.com writes: I wonder what is the future of _PG_fini, documentation say at [1]: Note that _PG_fini will only be called during an unload of the file, not during process termination. (Presently, unloads are disabled and will never occur, but this may change in the future.) What we'd need to work out before (re)enabling _PG_fini is some consistent rules for allowing multiple modules to get into *and out of* the same hook pointers. The current coding methods are very load-order-dependent, and that would have to be fixed somehow. 1. do we want a _PG_fini which is call on server stop ? 2. what's actually the best way to execute some code when server stop, if one have ideas ... ? In any case, _PG_fini would have approximately nothing to do with code to be executed on server stop. It would happen at session end, typically. Personally I'd suggest putting whatever you have in mind into your service start/stop scripts. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 8:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: If we're going to implement something whose ambitions only extend to satisfying pg_migrator's needs, then it should be a specialized pg_migrator function. Fwiw my feeling was the opposite here. It's better to offer even limited SQL-level support for features pg_migrator needs because the more abstract and loosely coupled the interface is between pg_migrator and the internals the better. Even if the interface is somewhat limited and just good enough for pg_migrator's needs it's still easier to support a well-defined abstract interface than one that depends on knowing about the internal implementation. The problem is that we *don't* want a nice abstract interface. We want one that lets us specify the exact OIDs to use for the enum values. Which is about as non-abstract as you can get. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what about _PG_fini
2009/12/23 Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-1?Q?C=E9dric_Villemain?= cedric.villemain.deb...@gmail.com writes: I wonder what is the future of _PG_fini, documentation say at [1]: Note that _PG_fini will only be called during an unload of the file, not during process termination. (Presently, unloads are disabled and will never occur, but this may change in the future.) What we'd need to work out before (re)enabling _PG_fini is some consistent rules for allowing multiple modules to get into *and out of* the same hook pointers. The current coding methods are very load-order-dependent, and that would have to be fixed somehow. Ok, thank you for clarification. 1. do we want a _PG_fini which is call on server stop ? 2. what's actually the best way to execute some code when server stop, if one have ideas ... ? In any case, _PG_fini would have approximately nothing to do with code to be executed on server stop. It would happen at session end, typically. Personally I'd suggest putting whatever you have in mind into your service start/stop scripts. Yes, I thought it was probably the simplest way to do it. for information I have some functions to make a snapshot of the blocks which are in buffer cache (it is a max of 32KB per segment) and to reload them when server start. Option to execute them without 'external' code could have been fine. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] About the CREATE TABLE LIKE indexes vs constraints issue
On Wed, 2009-12-23 at 14:55 -0500, Tom Lane wrote: I think the most natural reading of the syntax would be INCLUDING INDEXES means to include everything you made with CREATE INDEX syntax, while INCLUDING CONSTRAINTS means to include everything you made with CONSTRAINT syntax. Agreed. However, it's unclear whether that's so much better or more useful as to justify a compatibility break. Arguably, lumping all indexes together is the most useful behavior in practice. Probably so. LIKE is shorthand anyway, and I think the most useful thing in practice would be to get everything (indexes and constraints). Another spanner in the works is that foreign-key constraints aren't copied. I see why FKs aren't always copied: Let's say you have two tables, one with a FK referencing the other. If you want to create two parallel tables that are like the two originals, you might want one new table to reference the other new table, rather than the original. But if you say including constraints, the POLA would probably require copying the FKs, as well. Do we want to try to clean this up, or should we leave it alone on backwards-compatibility grounds? I don't have a strong opinion. It's DDL time only, so the only breakage I can imagine is someone's version-controlled DDL. I would think that most of the people who use LIKE are probably doing it as a one-off. So it wouldn't be the end of the world to break compatibility here. Honestly, I've never used LIKE in a table definition aside from one-off design experiments. For that kind of thing, what I want is to just get everything (except perhaps FKs if the above situation applies), and I adjust it from there. Are there people out there who use LIKE in their production schema files? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 7:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: The reason that isn't implemented is that it's *hard* --- in fact, it appears to be entirely impossible in the general case, unless you're willing to change existing values of the enum on-disk. Shouldn't adding new ones be easy? No, not if you care about where they end up in the type's sort ordering. In pg_migrator's case that's not an issue because it's going to force the OID numbering for each of the elements. However, an ADD ENUM VALUE option that *doesn't* use a predetermined OID is going to end up inserting the new value at a not-very-predictable place. I do not think we should expose a half-baked behavior like that as standard SQL syntax. If we're going to implement something whose ambitions only extend to satisfying pg_migrator's needs, then it should be a specialized pg_migrator function. I looked at DefineEnum() and basically adding the ability to add enums would put the new enum after the existing ones unless the OID counter has wrapped around and is less than the oid counter at the time the enum type was created, in which case it will be listed as before the existing values. I wasn't aware enum ordering is something we tried to maintain. One issue is that we are not supporting the addition of enum values even for people who don't care about the ordering of enums (which I bet might be the majority.) I can think of a few approaches for pg_migrator: 1) Create an oid array in a permanent memory context and have DefineEnum() read from that. 2) Renumber the enum entries after they are created but before any of their oids are stored in user tables. Both can be done by pg_dump with proper server-side functions. The problem with #2 are cases where the old and new oid ranges overlap, e.g.: 1 2 3 becomes: 2 3 4 In that case, you can't just renumber because of oid collisions that would invalidate the oid index on pg_enum. Even the ordering of renumbering might not be consistent, e.g.: old 1 2 3 12 13 14 new 2 3 4 11 12 13 Starting renumbering from the front or back would both fail. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] About the CREATE TABLE LIKE indexes vs constraints issue
Jeff Davis pg...@j-davis.com writes: Honestly, I've never used LIKE in a table definition aside from one-off design experiments. For that kind of thing, what I want is to just get everything (except perhaps FKs if the above situation applies), and I adjust it from there. Are there people out there who use LIKE in their production schema files? The only concrete application I've heard of for LIKE is to create something that is going to be part of a partitioned table later. That is, instead of create table partition23 () inherits (partition_parent); you do create table partition23 (like partition_parent); ... alter table partition23 inherit partition_parent; The advantage of the latter is you can tweak the new partition (eg, load data into it) before you make it a live part of the partition set. So in this context it's entirely likely that people would be using LIKE in scripted procedures. However, it's not immediately obvious to me whether the current definitions of the LIKE options are well suited to this application. The lack of any support for copying foreign keys seems a bit questionable for instance. Now if you plan a bulk load before taking the partition live, maybe you'd not want to enable foreign key checks till after --- but the same would hold for indexes, so why is there an option to copy one but not the other? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] About the CREATE TABLE LIKE indexes vs constraints issue
Jeff Davis wrote: ... Honestly, I've never used LIKE in a table definition aside from one-off design experiments. For that kind of thing, what I want is to just get everything (except perhaps FKs if the above situation applies), and I adjust it from there. Are there people out there who use LIKE in their production schema files? I for one never have either. If I needed such a thing I'd go through the steps of using pg_dump to get the structure, edit the result to get my new table's name and structure, and then use that SQL to create the table and add it to the CVS so I would have a record of it if I ever needed to refer to it later (when did I do this?) or if I needed to recreate the scheme because of a crash or whatever. Too easy to make new table with LIKE and not have the proper record of how to recreate the changes, IMHO. (Of course you could capture the SQL w/ history and use that.) Greg W. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Bruce Momjian wrote: I wasn't aware enum ordering is something we tried to maintain. One issue is that we are not supporting the addition of enum values even for people who don't care about the ordering of enums (which I bet might be the majority.) The ordering of enums is defined and to be relied on and I think it's absolutely unacceptable not to be able to rely on the ordering. We should never be in a position where the values returned by enum_first(), enum_range() etc. are not completely deterministic. Part of the original motivation for implementing enums was precisely so that they would sort in the defined order rather than in lexicographical order. It's a fundamental part of the type and not an optional feature. The idea of potentially breaking it makes no more sense than allowing for a non-deterministic ordering of integers. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing pg_migrator limitations
Andrew Dunstan wrote: Bruce Momjian wrote: I wasn't aware enum ordering is something we tried to maintain. One issue is that we are not supporting the addition of enum values even for people who don't care about the ordering of enums (which I bet might be the majority.) The ordering of enums is defined and to be relied on and I think it's absolutely unacceptable not to be able to rely on the ordering. We should never be in a position where the values returned by enum_first(), enum_range() etc. are not completely deterministic. I had no idea we exposed that API. Part of the original motivation for implementing enums was precisely so that they would sort in the defined order rather than in lexicographical order. It's a fundamental part of the type and not an optional feature. The idea of potentially breaking it makes no more sense than allowing for a non-deterministic ordering of integers. OK, I get the point. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] join ordering via Simulated Annealing
Em 22-12-2009 22:23, Jan Urbański escreveu: o) the initial state is not really a random plan, it's usualy a left-deep tree (and is very inefficient) and this might skew results. Maybe a QuickPick + SA. http://www.springerlink.com/content/garn64gt61ju5xfa/ http://portal.acm.org/citation.cfm?doid=1559845.1559889 Att Adriano Lange -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Dec 23, 2009, at 1:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Dec 23, 2009, at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Uh ... I don't see what that fixes? If CONCURRENTLY can be a column name this is still ambiguous. How? Because CONCURRENTLY can still be reduced as tricky_index_name, so it still doesn't know how to parse CREATE INDEX CONCURRENTLY ON ... It compiles without warnings for me. There's only one production that allows exactly one word between INDEX and ON. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
