Re: [HACKERS] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Would it be an idea to issue a CLUSTER command after the CREATE TABLE statement in SQL dumps for tables that have an indisclustered index? Yeah... snip A CLUSTER command issued just after table creation, while it's still empty, would be cheap ... but we don't put the index in place until we've loaded the data, do we? Darn. CREATE CLUSTERED INDEX ...? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Linking to current docs
With the old idocs it was possible to create links to the current version of individual documentation pages without having to provide a Postgres version number, e.g.: http://www.PostgreSQL.org/idocs/index.php?kernel-resources.html which would remain constant over longer periods of time. Providing no major restructuring of the docs takes place, this is a useful low-maintenance way of linking to the docs without worrying too much about version changes and seems to be used in a variety of places. With the new documentation at http://www.postgresql.org/docs/ (which is BTW very nice) this approach doesn't appear to work :-(. Would it be possible to modify the new docs to provide similar functionality? E.g. something like http://www.postgresql.org/docs/view.php?version=currentidoc=0file=sql-createtable.html If neccessary I can provide time to do this (though I have no idea of what would be involved ;-). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bug in contrib/adddepend
See how it's HTMLised the foreign key name? I cannot find how $keyname in the code is being html escaped. Perhaps it's some weird taint mode thing? Not to mention when it's unnamed, the code is setup to ignore the constraint name entirely (line 267). I worked around it by checking to see if it equalled 'lt;unnamedgt;' as well as 'unnamed'. I also have heaps of RI triggers left over after the addepend run, so I'll investigate why adddepend doesn't pick them up... There isn't anything that changes the value of $keyname after it's pulled from the DBI drivers. What version of DBI::Pg are you using? Last time I used it was with DBI::Pg v1.13. I'm using: p5-DBD-Pg-1.21 Provides access to PostgreSQL databases through the p5-DBI-1.28 The perl5 Database Interface. Required for DBD::* Chris ---(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
[HACKERS] Allow pg_dump to dump sequences using NO_MAXVALUE and NO_MINVALUEb
- Move SEQ_MAXVALUE, SEQ_MINVALUE definitions to sequence.h - Add check in pg_dump to see if the value returned is the max /min values and replace with NO MAXVALUE, NO MINVALUE. - Change START and INCREMENT to use START WITH and INCREMENT BY syntax. This makes it a touch easier to port to other databases with sequences (Oracle). PostgreSQL supports both syntaxes already. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc Index: src/backend/commands/sequence.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/sequence.c,v retrieving revision 1.91 diff -c -r1.91 sequence.c *** src/backend/commands/sequence.c 2003/02/13 05:25:24 1.91 --- src/backend/commands/sequence.c 2003/02/23 13:27:57 *** *** 24,42 #include utils/acl.h #include utils/builtins.h - - #ifndef INT64_IS_BUSTED - #ifdef HAVE_LL_CONSTANTS - #define SEQ_MAXVALUE ((int64) 0x7FFFLL) - #else - #define SEQ_MAXVALUE ((int64) 0x7FFF) - #endif - #else /* INT64_IS_BUSTED */ - #define SEQ_MAXVALUE ((int64) 0x7FFF) - #endif /* INT64_IS_BUSTED */ - - #define SEQ_MINVALUE (-SEQ_MAXVALUE) - /* * We don't want to log each fetching of a value from a sequence, * so we pre-log a few fetches in advance. In the event of --- 24,29 Index: src/bin/pg_dump/pg_dump.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.318 diff -c -r1.318 pg_dump.c *** src/bin/pg_dump/pg_dump.c 2003/02/13 22:56:52 1.318 --- src/bin/pg_dump/pg_dump.c 2003/02/23 13:29:23 *** *** 52,57 --- 52,59 #include catalog/pg_trigger.h #include catalog/pg_type.h + #include commands/sequence.h + #include libpq-fe.h #include libpq/libpq-fs.h *** *** 5986,5994 PGresult *res; char *last, *incby, ! *maxv, ! *minv, *cache; bool cycled, called; PQExpBuffer query = createPQExpBuffer(); --- 5988,5998 PGresult *res; char *last, *incby, ! *maxv = NULL, ! *minv = NULL, *cache; + char bufm[100], + bufx[100]; bool cycled, called; PQExpBuffer query = createPQExpBuffer(); *** *** 5997,6005 /* Make sure we are in proper schema */ selectSourceSchema(tbinfo-relnamespace-nspname); appendPQExpBuffer(query, ! SELECT sequence_name, last_value, increment_by, max_value, ! min_value, cache_value, is_cycled, is_called from %s, fmtId(tbinfo-relname)); res = PQexec(g_conn, query-data); --- 6001,6021 /* Make sure we are in proper schema */ selectSourceSchema(tbinfo-relnamespace-nspname); + snprintf(bufm, 100, INT64_FORMAT, SEQ_MINVALUE); + snprintf(bufx, 100, INT64_FORMAT, SEQ_MAXVALUE); + appendPQExpBuffer(query, ! SELECT sequence_name, last_value, increment_by, ! CASE WHEN increment_by 0 AND max_value = %s THEN NULL ! WHEN increment_by 0 AND max_value = -1 THEN NULL ! ELSE max_value ! END AS max_value, ! CASE WHEN increment_by 0 AND min_value = 1 THEN NULL ! WHEN increment_by 0 AND min_value = %s THEN NULL ! ELSE min_value ! END AS min_value, ! cache_value, is_cycled, is_called from %s, ! bufx, bufm, fmtId(tbinfo-relname)); res = PQexec(g_conn, query-data); *** *** 6028,6035 last = PQgetvalue(res, 0, 1); incby = PQgetvalue(res, 0, 2); ! maxv = PQgetvalue(res, 0, 3); ! minv = PQgetvalue(res, 0, 4); cache = PQgetvalue(res, 0, 5); cycled = (strcmp(PQgetvalue(res, 0, 6), t) == 0); called = (strcmp(PQgetvalue(res, 0, 7), t) == 0); --- 6044,6053 last = PQgetvalue(res, 0, 1); incby = PQgetvalue(res, 0, 2); ! if (!PQgetisnull(res, 0, 3)) ! maxv = PQgetvalue(res, 0, 3); ! if (!PQgetisnull(res, 0, 4)) ! minv = PQgetvalue(res, 0, 4); cache = PQgetvalue(res, 0, 5); cycled = (strcmp(PQgetvalue(res, 0, 6), t) == 0); called = (strcmp(PQgetvalue(res, 0, 7), t) == 0); *** *** 6060,6071 resetPQExpBuffer(query); appendPQExpBuffer(query, ! CREATE SEQUENCE %s\nSTART %s\nINCREMENT %s\n ! MAXVALUE %s\nMINVALUE %s\nCACHE %s%s;\n, fmtId(tbinfo-relname), ! (called ? minv : last), ! incby, maxv, minv, cache, ! (cycled ? \nCYCLE : )); ArchiveEntry(fout, tbinfo-oid, tbinfo-relname, tbinfo-relnamespace-nspname, tbinfo-usename, --- 6078,6100 resetPQExpBuffer(query); appendPQExpBuffer(query, ! CREATE SEQUENCE %s\nSTART WITH %s\nINCREMENT BY %s\n, fmtId(tbinfo-relname), ! (called ? minv : last), incby); ! ! if (maxv) ! appendPQExpBuffer(query, MAXVALUE %s\n, maxv); ! else !
Re: [HACKERS] Allow pg_dump to dump sequences using NO_MAXVALUE and
On 23 Feb 2003, Rod Taylor wrote: - Move SEQ_MAXVALUE, SEQ_MINVALUE definitions to sequence.h - Add check in pg_dump to see if the value returned is the max /min values and replace with NO MAXVALUE, NO MINVALUE. - Change START and INCREMENT to use START WITH and INCREMENT BY syntax. This makes it a touch easier to port to other databases with sequences (Oracle). PostgreSQL supports both syntaxes already. + charbufm[100], + bufx[100]; This seems to be an arbitary size. Why not set it to the actual maximum length? Also: + snprintf(bufm, 100, INT64_FORMAT, SEQ_MINVALUE); + snprintf(bufx, 100, INT64_FORMAT, SEQ_MAXVALUE); sizeof(bufm), sizeof(bufx) is probably the more maintenance-friendly/standard way to do it. Thanks, Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump / restore of empty database gives errors
Rod Taylor [EMAIL PROTECTED] writes: ERROR: dependent privileges exist (use CASCADE to revoke them too) I saw that a couple weeks ago, and then was unable to reproduce it later Seems to be by design. Create an empty schema with no permissions. First REVOKE sees lack of permissions, and adds them for the owner. The GRANT makes PUBLIC dependent on the owner for permissions. The second REVOKE attempts to remove the permissions of the owner (replace CURRENT_USER with the current user) which PUBLIC is now dependent upon. Hmm. So the real story here is that the permissions set up by initdb for PUBLIC are actually an illegal state: postgres has granted permissions to public that it isn't allowed to. When pg_dump tries to reproduce that state, it can't. (There may also be an issue with the order in which pg_dump issues its revoke/grant operations, ie, there might be legal combinations that it can't reproduce.) Peter, what do you think? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] locking mechanism
hi, i wanted to know whether in postgresql, asingleprocess can wait on acquiring multiple locks and if can hold multiple locks at the same time? thanks sumairaMSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.
Re: [HACKERS] bug in contrib/adddepend
I worked around it by checking to see if it equalled 'lt;unnamedgt;' as well as 'unnamed'. I also have heaps of RI triggers left over after the addepend run, so I'll investigate why adddepend doesn't pick them up... Took a quick look, and I don't see anything in DBD-Pg that would do this type of conversion. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] locking mechanism
On Sun, 2003-02-23 at 13:19, Sumaira Ali wrote: hi, i wanted to know whether in postgresql, a single process can wait on acquiring multiple locks and if can hold multiple locks at the same time? http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=sql-lock.html The docs answer those questions. No to the first, yes to the second. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] Unclear documentation of to_char()
The documentation of to_char() is unclear regarding the meaning of the various formatting patterns for plus and minus signs: S negative value with minus sign (uses locale) MI minus sign in specified position (if number 0) PL plus sign in specified position (if number 0) SG plus/minus sign in specified position Here is what happens: (The quotes in the result are not actually part of the function result.) to_char(485.8, 'S.999') ' +485.800' to_char(485.8, 'MI.999')' 485.800' to_char(485.8, 'PL.999')'+ 485.800' to_char(485.8, 'SG.999')'+ 485.800' to_char(-485.8, 'S.999')' -485.800' to_char(-485.8, 'MI.999') '- 485.800' to_char(-485.8, 'PL.999') ' -485.800' to_char(-485.8, 'SG.999') '- 485.800' to_char(485.8, '.999S') ' 485.800+' to_char(485.8, '.999PL')' 485.800+' to_char(485.8, '.999MI')' 485.800 ' to_char(485.8, '.999SG')' 485.800+' to_char(-485.8, '.999S')' 485.800-' to_char(-485.8, '.999PL') ' -485.800 ' to_char(-485.8, '.999MI') ' 485.800-' to_char(-485.8, '.999SG') ' 485.800-' The SG seems to work okay, and the S could be considered okay if the documentation were adjusted, but I suspect there are a few bugs in PL and MI. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Linking to current docs
-Original Message- From: Ian Barwick [mailto:[EMAIL PROTECTED] Sent: 23 February 2003 10:56 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [HACKERS] Linking to current docs Would it be possible to modify the new docs to provide similar functionality? E.g. something like http://www.postgresql.org/docs/view.php?version=currentidoc=0file=sql- createtable.html Done. Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump / restore of empty database gives errors
Tom Lane writes:
Hmm. So the real story here is that the permissions set up by initdb
for PUBLIC are actually an illegal state: postgres has granted
permissions to public that it isn't allowed to.
Yes, the way the permissions are initialized in the catalog templates
DATA(insert OID = 11 ( pg_catalog PGUID {=U} ));
DATA(insert OID = 99 ( pg_toast PGUID {=} ));
DATA(insert OID = 2200 ( public PGUID {=UC} ));
produce an invalid state. I hadn't thought that this would create a
problem for pg_dump, but I will hurry up fixing it. (I will probably put
explicit GRANT commands into initdb.)
(There may also be an issue with the order in which pg_dump issues its
revoke/grant operations, ie, there might be legal combinations that it
can't reproduce.)
If you don't do manual surgery on aclitem's then I am convinced that it is
not possible to arrive at an undumpable state. This is a consequence of
the way it's implemented.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Loss of cluster status
No; directly manipulating the system catalogs in dump scripts is a crummy idea, because (a) it only works if you're superuser, and (b) it creates a nasty backwards-compatibility problem if we change the catalogs involved. A CLUSTER command issued just after table creation, while it's still empty, would be cheap ... but we don't put the index in place until we've loaded the data, do we? Darn. Maybe we should issue it after the CREATE INDEX and ADD CONSTRAINT has occurred and just bite it. We could have a pg_dump --no-cluster option to suppress them. However, we need to guarantee to the user that we restore their database exactly as they had it. Other potential problem - ALTER TABLE / SET STORAGE ? Chris ---(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] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] writes: A CLUSTER command issued just after table creation, while it's still empty, would be cheap ... but we don't put the index in place until we've loaded the data, do we? Darn. Maybe we should issue it after the CREATE INDEX and ADD CONSTRAINT has occurred and just bite it. The real problem I think is that we've confused the notion of setting a policy for CLUSTER (ie, marking the preferred thing to cluster on) with the notion of actually doing a CLUSTER. Perhaps we need an ALTER command that says this is what to cluster on without actually doing it. Other potential problem - ALTER TABLE / SET STORAGE ? Yeah, pg_dump should be dumping that too, probably. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
Josh Berkus [EMAIL PROTECTED] writes: - Some other databases support ILIKE and it makes porting easier. Which other ones? I checked our archives and found that when we were discussing adding ILIKE, it was claimed that Oracle had it. But I can't find anything on the net to verify that claim. I did find that mSQL (not MySQL) had it, as far back as 1996. Nothing else seems to --- but Google did provide a lot of hits on pages saying that ILIKE is a mighty handy Postgres-ism ;-) Why this sudden urge to prune away perfectly useful operators? My feeling too. Whatever you may think of its usefulness, it's been a documented feature since 7.1. It's a bit late to reconsider. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Peter,
Several reasons (because I like lists):
- Some other databases support ILIKE and it makes porting easier.
- For tables and/or subqueries that are too small to need an index, ILIKE is
perfectly acceptable.
- It's also useful for comparing expressions, and is faster to type than
'jehosaphat' ~* '^Jehosaphat$', and certainly much faster than
lower('jehosaphat') = lower('Jehosaphat')
Why this sudden urge to prune away perfectly useful operators?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
