Re: [HACKERS] Another python patch -- minor
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Greg Copeland wrote:
Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> This fixes some text as well as enforces the use of "drop table cascade"
> since we moved from an implicate to explicate implementation.
>
> Please find attached the func.py patch.
>
> Sorry these are not all one single patch. I really hadn't planned on
> doing all this...especially not tonight. ;)
>
> Greg Copeland
>
>
>
[ text/x-patch is unsupported, treating like TEXT/PLAIN ]
> Index: func.py
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/tutorial/func.py,v
> retrieving revision 1.5
> diff -u -r1.5 func.py
> --- func.py 2000/10/02 03:46:24 1.5
> +++ func.py 2002/08/08 03:47:04
> @@ -9,7 +9,7 @@
> This module is designed for being imported from python prompt
>
> In order to run the samples included here, first create a connection
> -using :cnx = advanced.DB(...)
> +using :cnx = func.DB(...)
>
> The "..." should be replaced with whatever arguments you need to open an
> existing database. Usually all you need is the name of the database and,
> @@ -189,13 +189,13 @@
> print "DROP FUNCTION add_em(int4, int4)"
> print "DROP FUNCTION one()"
> print
> - print "DROP TABLE EMP"
> + print "DROP TABLE EMP CASCADE"
> pgcnx.query("DROP FUNCTION clean_EMP()")
> pgcnx.query("DROP FUNCTION high_pay()")
> pgcnx.query("DROP FUNCTION new_emp()")
> pgcnx.query("DROP FUNCTION add_em(int4, int4)")
> pgcnx.query("DROP FUNCTION one()")
> - pgcnx.query("DROP TABLE EMP")
> + pgcnx.query("DROP TABLE EMP CASCADE")
>
> # main demo function
> def demo(pgcnx):
-- End of PGP section, PGP failed!
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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] python patch
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Greg Copeland wrote:
Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> Well, that certainly appeared to be very straight forward. pg.py and
> syscat.py scripts were both modified. pg.py uses it to cache a list of
> pks (which is seemingly does for every db connection) and various
> attributes. syscat uses it to walk the list of system tables and
> queries the various attributes from these tables.
>
> In both cases, it seemingly makes sense to apply what you've requested.
>
> Please find attached the quested patch below.
>
> Greg
>
>
> On Wed, 2002-08-07 at 22:16, Christopher Kings-Lynne wrote:
> > > I don't have a problem looking into it but I can't promise I can get it
> > > right. My python skills are fairly good...my postgres internal skills
> > > are still sub-par IMO.
> > >
> > > From a cursory review, if attisdropped is true then the attribute/column
> > > should be ignored/skipped?! Seems pretty dang straight forward.
> >
> > Basically, yep. Just grep the source code for pg_attribute most likely...
> >
> > I'm interested in knowing what it uses pg_attribute for as well...?
> >
> > 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
>
[ text/x-patch is unsupported, treating like TEXT/PLAIN ]
> Index: pg.py
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/pg.py,v
> retrieving revision 1.9
> diff -u -r1.9 pg.py
> --- pg.py 2002/03/19 13:20:52 1.9
> +++ pg.py 2002/08/08 03:29:48
> @@ -69,7 +69,8 @@
> WHERE pg_class.oid =
>pg_attribute.attrelid AND
> pg_class.oid =
>pg_index.indrelid AND
> pg_index.indkey[0] =
>pg_attribute.attnum AND
> - pg_index.indisprimary =
>'t'""").getresult():
> + pg_index.indisprimary = 't' AND
> + pg_attribute.attisdropped =
>'f'""").getresult():
> self.__pkeys__[rel] = att
>
> # wrap query for debugging
> @@ -111,7 +112,8 @@
> WHERE pg_class.relname = '%s' AND
> pg_attribute.attnum > 0 AND
> pg_attribute.attrelid = pg_class.oid
>AND
> - pg_attribute.atttypid = pg_type.oid"""
> + pg_attribute.atttypid = pg_type.oid AND
> + pg_attribute.attisdropped = 'f'"""
>
> l = {}
> for attname, typname in self.db.query(query % cl).getresult():
> Index: tutorial/syscat.py
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/tutorial/syscat.py,v
> retrieving revision 1.7
> diff -u -r1.7 syscat.py
> --- tutorial/syscat.py2002/05/03 14:21:38 1.7
> +++ tutorial/syscat.py2002/08/08 03:29:48
> @@ -37,7 +37,7 @@
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> WHERE i.indrelid = bc.oid AND i.indexrelid = bc.oid
> AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
> - AND i.indproc = '0'::oid
> + AND i.indproc = '0'::oid AND a.attisdropped = 'f'
> ORDER BY class_name, index_name, attname""")
> return result
>
> @@ -48,6 +48,7 @@
> WHERE c.relkind = 'r' and c.relname !~ '^pg_'
> AND c.relname !~ '^Inv' and a.attnum > 0
> AND a.attrelid = c.oid and a.atttypid = t.oid
> +AND a.attisdropped = 'f'
> ORDER BY relname, attname""")
> return result
>
-- End of PGP section, PGP failed!
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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] python patch
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Greg Copeland wrote:
Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> Okay, I read
> http://archives.postgresql.org/pgsql-bugs/2002-06/msg00086.php and never
> saw a fix offered up. Since I'm gearing up to use Postgres and Python
> soon, I figured I'd have a hand at trying to get this sucker addressed.
> Apologies if this has already been plugged. I looked in the archives
> and never saw a response.
>
> At any rate, I must admit I don't think I fully understand the
> implications of some of the changes I made even though they appear to be
> straight forward. We all know the devil is in the details. Anyone more
> knowledgeable is requested to review my changes. :(
>
> I also updated the advanced.py script in a somewhat nonsensical fashion
> to make use of an int8 field in an effort to test this change. It seems
> to run okay, however, this is by no means an all exhaustive test. So,
> it's possible that a bumpy road may lay ahead for some. On the other
> hand...overflows (hopefully) previously lurked (long -> int conversion).
>
> This is my first submission. Please be kind if I submitted to the wrong
> list. ;)
>
> Thank you,
> Greg Copeland
>
[ text/x-diff is unsupported, treating like TEXT/PLAIN ]
> ? lib_pgmodule.so.0.0
> ? postgres-python.patch
> ? tutorial/advanced.pyc
> Index: pgmodule.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/pgmodule.c,v
> retrieving revision 1.38
> diff -u -r1.38 pgmodule.c
> --- pgmodule.c2002/03/29 07:45:39 1.38
> +++ pgmodule.c2002/08/08 02:46:12
> @@ -289,23 +289,26 @@
> {
> case INT2OID:
> case INT4OID:
> - case INT8OID:
> case OIDOID:
> typ[j] = 1;
> break;
>
> + case INT8OID:
> + typ[j] = 2;
> + break;
> +
> case FLOAT4OID:
> case FLOAT8OID:
> case NUMERICOID:
> - typ[j] = 2;
> + typ[j] = 3;
> break;
>
> case CASHOID:
> - typ[j] = 3;
> + typ[j] = 4;
> break;
>
> default:
> - typ[j] = 4;
> + typ[j] = 5;
> break;
> }
> }
> @@ -1797,23 +1800,26 @@
> {
> case INT2OID:
> case INT4OID:
> - case INT8OID:
> case OIDOID:
> typ[j] = 1;
> break;
>
> + case INT8OID:
> + typ[j] = 2;
> + break;
> +
> case FLOAT4OID:
> case FLOAT8OID:
> case NUMERICOID:
> - typ[j] = 2;
> + typ[j] = 3;
> break;
>
> case CASHOID:
> - typ[j] = 3;
> + typ[j] = 4;
> break;
>
> default:
> - typ[j] = 4;
> + typ[j] = 5;
> break;
> }
> }
> @@ -1846,10 +1852,14 @@
> break;
>
> case 2:
> - val = PyFloat_FromDouble(strtod(s,
>NULL));
> + val = PyLong_FromLong(strtol(s, NULL,
>10));
> break;
>
> case 3:
> + val = PyFloat_FromDouble(strtod(s,
>NULL));
> + break;
> +
> + case 4:
> {
> int mult =
>1;
>
> @@ -1946,11 +1956,14 @@
> {
> case INT2OID:
> case INT4OID:
> - case INT8OID:
> case OIDOID:
> typ[j] = 1;
> break;
Re: [HACKERS] Open 7.3 items
I would like to address this email.
Lamar is mentioning that it is unfair to remove a feature without
warning.
Let me give a little history. The secondary password file was created
at a time when we didn't encrypt with random salt over the wire, and we
had people who wanted to share their /etc/passwd file with PostgreSQL.
Later, people wanted to use the secondary password file for just
usernames, so you could list usernames in the file and limit db access
by user. This is the current usage for 99% of secondary password users.
This capability is better served in 7.3 with the new USER column in
pg_shadow and the ability to specify filenames or groups in that file.
Keeping the secondary password file to specify a user list while a new
USER column exists in 7.3 is just confusing to administrators. Our
pg_hba.conf system is pretty complex, so anything we can do to simplify
helps.
Now, on to Marc's case, where he does use the file for usernames _and_
passwords. However, he is using it only so he can have more than one
person with the same username and restrict access based on the password
in the secondary password file. While this does work, my submitted
patch makes this much easier and cleaner.
Marc had mentioned that this should be an initdb flag. However, our
standard procedure is to put stuff in initdb only when it can't be
changed after initdb. While strange, this feature can be
enabled-disabled after initdb. A quick update of pg_shadow can change
usernames and you can go in and out of this mode.
Someone talked about pushing this capability into a separate pg_shadow
column, and making CREATE/ALTER user and createuser aware of this.
While this can be done, and it sort of becomes user schemas, there isn't
enough people wanting this to add complexity to those commands. A GUC
flag will meet most peoples needs at this point.
Some mentioned using user@dbname, though the idea of sorting made
several recant their votes.
So, based on the voting, I think dbname.username is an agreed-upon
feature addition for 7.3. I will work on a final patch with
documentation and post it to the patches list for more comment.
---
Lamar Owen wrote:
> On Tuesday 06 August 2002 09:24 pm, Marc G. Fournier wrote:
> > On Tue, 6 Aug 2002, Bruce Momjian wrote:
> > > It had such limited usefulness ('password' only, only crypted-hashed
> > > passwords in the file) that it doesn't make much sense to resurect it.
>
> > It had limited usefulness to you ... but how many sites out there are
> > going to break when they try to upgraded without it there? I do agree
> > that it needs to improved / replaced, but without a suitable replacement
> > in place, the old should be resurrected until such a suitable one is in
> > place ...
>
> While it appears I'll be outvoted on this issue, and even though I agree that
> the existing functionality is broken, and even though I am not using the
> functionality, I am reminded of the overall policy that we have historically
> had about removing even broken features. Fair Warning must be given. If that
> policy is going to be changed, then it needs to be applied with equal vigor
> to all affected cases.
>
> Even if Marc is the only one using this feature, we should follow established
> policy -- that is, after all, what policy is for. To me it seems it is being
> yanked gratuitously without fair warning. If every question is answered on a
> case-by-case basis like this, we will descend to anarchy, I'm afraid. And,
> Bruce, I even agree with your reasons -- I just disagree with the method.
>
> Is it going to cause a major problem for it to remain one release cycle while
> someone works on a suitable replacement, with the warning in the release
> notes that while this feature is there for backwards compatibility that it
> will be yanked at the next release? And I'm not talking about a minor
> problem like 'more people will start using it' -- I'm talking 'if it stays we
> will be in danger of massive data corruption or exposure' -- of course,
> documenting that there is a degree of exposure of data if not set up in an
> exacting method, as Marc seems to have done.
>
> Some may say Marc has fair warning now -- but does anyone know for sure that
> NO ONE ELSE in the whole world isn't using this feature? Marc is more in the
> know than most, granted -- but if he found this use for the feature others
> may have as well that we don't even know about.
>
> But if the feature is not going to remain it needs to be prominently
> documented as being removed in the release notes.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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)---
Re: [HACKERS] FUNC_MAX_ARGS benchmarks
Bruce Momjian wrote: > OK, seems we have not come to a decision yet on this. > > Do we have agreement to increate FUNC_MAX_ARGS to 32? > > NAMEDATALEN will be 64 or 128 in 7.3. At this point, we better decide > which one we prefer. > > The conservative approach would be to go for 64 and perhaps increase it > again in 7.4 after we get feedback and real-world usage. If we go to > 128, we will have trouble decreasing it if there are performance > problems. I guess I'd also agree with: FUNC_MAX_ARGS 32 NAMEDATALEN 64 and work on the performance issues for 7.4. Joe ---(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] FUNC_MAX_ARGS benchmarks
Bruce Momjian <[EMAIL PROTECTED]> writes: > Do we have agreement to increate FUNC_MAX_ARGS to 32? I believe so. > NAMEDATALEN will be 64 or 128 in 7.3. At this point, we better decide > which one we prefer. > The conservative approach would be to go for 64 and perhaps increase it > again in 7.4 after we get feedback and real-world usage. If we go to > 128, we will have trouble decreasing it if there are performance > problems. It seems fairly clear to me that there *are* performance problems, at least in some scenarios. I think we should go to 64. There doesn't seem to be a lot of real-world demand for more than that, despite what the spec says ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FUNC_MAX_ARGS benchmarks
OK, seems we have not come to a decision yet on this. Do we have agreement to increate FUNC_MAX_ARGS to 32? NAMEDATALEN will be 64 or 128 in 7.3. At this point, we better decide which one we prefer. The conservative approach would be to go for 64 and perhaps increase it again in 7.4 after we get feedback and real-world usage. If we go to 128, we will have trouble decreasing it if there are performance problems. --- Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > >> I'm not sure about the trend of increasing standard deviation --- that > >> may reflect more disk I/O being done, and perhaps more checkpoints > >> occurring during the test. But in any case it's clear that there's a > >> nontrivial runtime cost here. Does a 10% slowdown bother you? > > > Hmmm -- didn't Neil do some kind of test that had different results, > > i.e. not much performance difference? > > Well, one person had reported a 10% slowdown in pgbench, but Neil saw > a 10% speedup. Given the well-known difficulty of getting any > reproducible numbers out of pgbench, I don't trust either number very > far; but unless some other folk are willing to repeat the experiment > I think we can only conclude that pgbench isn't affected much by > NAMEDATALEN. > > > I wonder if the large number of > > DDL commands in installcheck doesn't skew the results against longer > > NAMEDATALEN compared to other benchmarks? > > Depends on what you consider skewed, I suppose. pgbench touches only a > very small number of relations, and starts no new backends over the > length of its run, thus everything gets cached and stays cached. At > best I'd consider it an existence proof that some applications won't be > hurt. > > Do you have another application you'd consider a more representative > benchmark? > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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] AnonCVS woes
> > (/projects/cvsroot/interfaces/libpqxx/debian/#cvs.lock): Permission > > denied > > cvs server: failed to obtain dir lock in repository > > `/projects/cvsroot/interfaces/libpqxx/debian' > > cvs [server aborted]: read lock failed - giving up > > I only just added that directory, so chances are you were trying to > check it out while I was committing it. > > Could you try again? Figured as much. But it would appear that this file isn't being blown away from the anoncvs server. Marc is probably using rsync without --delete. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] AnonCVS woes
On Sat, Aug 10, 2002 at 06:05:27PM -0400, Rod Taylor wrote: > P src/interfaces/libpqxx/configure.ac > cvs server: Updating src/interfaces/libpqxx/config > U src/interfaces/libpqxx/config/.cvsignore > cvs server: Updating src/interfaces/libpqxx/debian > cvs server: failed to create lock directory for > `/projects/cvsroot/interfaces/libpqxx/debian' > (/projects/cvsroot/interfaces/libpqxx/debian/#cvs.lock): Permission > denied > cvs server: failed to obtain dir lock in repository > `/projects/cvsroot/interfaces/libpqxx/debian' > cvs [server aborted]: read lock failed - giving up I only just added that directory, so chances are you were trying to check it out while I was committing it. Could you try again? Jeroen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] AnonCVS woes
P src/interfaces/libpqxx/configure.ac cvs server: Updating src/interfaces/libpqxx/config U src/interfaces/libpqxx/config/.cvsignore cvs server: Updating src/interfaces/libpqxx/debian cvs server: failed to create lock directory for `/projects/cvsroot/interfaces/libpqxx/debian' (/projects/cvsroot/interfaces/libpqxx/debian/#cvs.lock): Permission denied cvs server: failed to obtain dir lock in repository `/projects/cvsroot/interfaces/libpqxx/debian' cvs [server aborted]: read lock failed - giving up ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Dump serials as serial
I intend to make 'serial' and 'serial8' domains of the int4 and int8 datatypes. Now they're regular types and columns in \d will be marked as such. This leaves analyze.c to create the defaults but no longer will it be doing any datatype conversions. pg_dump and psql will be taught to ignore the default of 'serial' and 'serial8' datatypes. Perhaps a 'system_generated' flag on pg_attrdef would be more appropriate? We'd also be going with the assumption in pg_dump that a sequence name will be generated the same way in future versions (see prior discussion in hackers). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/ltree, pls, apply patch
Patch applied. Thanks. --- Teodor Sigaev wrote: > The patch solves this problem, I hope... > > > Christopher Kings-Lynne wrote: > > I'm still getting ltree failures on 64bit freebsd: > > > > sed 's,MODULE_PATHNAME,$libdir/ltree,g' ltree.sql.in >ltree.sql > > gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPI > > C -DLOWER_NODE -I. -I../../src/include -c -o ltree_io.o ltree_io.c -MMD > > ltree_io.c: In function `ltree_in': > > ltree_io.c:57: warning: int format, different type arg (arg 3) > > ltree_io.c:63: warning: int format, different type arg (arg 4) > > ltree_io.c:68: warning: int format, different type arg (arg 3) > > ltree_io.c:78: warning: int format, different type arg (arg 4) > > ltree_io.c: In function `lquery_in': > > ltree_io.c:185: warning: int format, different type arg (arg 3) > > ltree_io.c:193: warning: int format, different type arg (arg 3) > > ltree_io.c:197: warning: int format, different type arg (arg 3) > > ltree_io.c:202: warning: int format, different type arg (arg 3) > > ltree_io.c:207: warning: int format, different type arg (arg 3) > > ltree_io.c:217: warning: int format, different type arg (arg 4) > > ltree_io.c:226: warning: int format, different type arg (arg 4) > > ltree_io.c:231: warning: int format, different type arg (arg 3) > > ltree_io.c:233: warning: int format, different type arg (arg 3) > > ltree_io.c:243: warning: int format, different type arg (arg 3) > > ltree_io.c:251: warning: int format, different type arg (arg 3) > > ltree_io.c:260: warning: int format, different type arg (arg 3) > > ltree_io.c:265: warning: int format, different type arg (arg 3) > > ltree_io.c:273: warning: int format, different type arg (arg 3) > > ltree_io.c:279: warning: int format, different type arg (arg 3) > > ltree_io.c:296: warning: int format, different type arg (arg 4) > > > > I think it's to do with the printf % thingy used in the elog... > > > > Chris > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > > -- > Teodor Sigaev > [EMAIL PROTECTED] > [ application/gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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] strange performance anomalies
On 9 Aug 2002, Scott Shattuck wrote: > Hi, > > We recently put up a new 7.2.1 installation on Solaris 8 that serves a > 24x7 e-commerce site. The system seems to run pretty well most of the > time but we see a consistent form of performance anomaly. > > Watching pg_stat_activity the system spends most of it's time running > smoothly with queries clearing through sub-second. We have a production > job we run which immediately sent the site into a tailspin though. > Starting that job caused hundreds of select statements to queue up in > the pg_stat_activity view. This seems odd since MVCC would lead us to > believe that shouldn't happen. Readers shouldn't block wholesale like > that unless we're using DDL on the table or doing a vacuum per the > online docs at > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/locking-tables.html > > Nevertheless, turning off foreign key constraint checking via: > > update "pg_class" set "reltriggers" = 0 where "relname" = tablename > > cleared up the problem, load fell off to normal in seconds. So how is it > that fk contraints apparently cause what look like table-level locks? > Or, at the very least, cause a heck of a lot of select statements to go > into a holding pattern for some reason? If you insert/update a row which contains foreign keys then PostgreSQL will do a SELECT ... FOR UPDATE on the appropriate row in the primary key table. This will block any SELECT ... FOR UPDATE, UPDATE or DELETE statements affecting that row - including other foreign key checks. A commonly referenced primary key value can become the subject of quite a lot of lock contention; not to mention deadlocks. PostgreSQL will do this for EVERY field in the row which is being modified which has a foreign key constraint (unless that field is NULL). It will perform this check even if that field is not being changed. > At any rate, being somewhat new to tuning at this load level for PG I'm > not sure if I'm supposed to be tinkering with max_lock_per_transaction > here. Could this be evidence of a lock starvation issue or something? > Guessing here and any input would be appreciated. Thanks in advance! You might be best off just turning off the foreign key checks on your production server (or, at least, some of them) until someone gets round to coming up with some sort of fix for PostgreSQL. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Please, apply another patch to contrib/ltree
Patch applied. Thanks. --- Teodor Sigaev wrote: > Fixed very stupid but important bug: mixing calls of some founctions from > contrib/tsearch and contrib/ltree :) > > -- > Teodor Sigaev > [EMAIL PROTECTED] > [ application/gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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] apply patch for contrib/intarray (CVS)
Patch applied. Thanks. --- Oleg Bartunov wrote: > Please, apply patch for contrib/intarray (current CVS) > > Changes: > > August 6, 2002 >1. Reworked patch from Andrey Oktyabrski ([EMAIL PROTECTED]) with > functions: icount, sort, sort_asc, uniq, idx, subarray > operations: #, +, -, |, & > > FUNCTIONS: > > int icount(int[]) - the number of elements in intarray > int[] sort(int[], 'asc' | 'desc') - sort intarray > int[] sort(int[]) - sort in ascending order > int[] sort_asc(int[]),sort_desc(int[]) - shortcuts for sort > int[] uniq(int[]) - returns unique elements > int idx(int[], int item) - returns index of first intarray matching element >to item, or '0' if matching failed. > int[] subarray(int[],int START [, int LEN]) - returns part of intarray >starting from element number START (from 1) >and length LEN. > OPERATIONS: > > int[] && int[] - overlap - returns TRUE if arrays has at least one common >elements. > int[] @ int[] - contains - returns TRUE if left array contains right array > int[] ~ int[] - contained - returns TRUE if left array is contained in right >array > # int[] - return the number of elements in array > int[] + int - push element to array ( add to end of array) > int[] + int[] - merge of arrays (right array added to the end of left one) > int[] - int - remove entries matched by right argument from array > int[] - int[] - remove left array from right > int[] | int - returns intarray - union of arguments > int[] | int[] - returns intarray as a union of two arrays > int[] & int[] - returns intersection of arrays > > > > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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/users-lounge/docs/faq.html
Re: [HACKERS] pg_stat_reset() weirdness
Joe Conway <[EMAIL PROTECTED]> writes: > I guess I should know better than to jump to a conclusion. But I *was* > under the impression we were supposed to use the unused_oids script to > get a unique oid for a new function. Right, we do still insist that all hand-assigned OIDs be distinct, but that is a matter of bookkeeping simplicity and possible debugging advantage. The system should only care that the OIDs in any one catalog are unique. (If it were to assume more, we'd have trouble after OID wraparound, because we can't guarantee database-wide uniqueness then. We *can* guarantee per-table uniqueness, by means of unique indexes placed on OIDs --- you'll notice all the catalogs that use OIDs have such indexes.) > Actually, I don't see the regression failure here at all, now that I try > the patch. Hmm. Maybe Chris just needs a make clean/rebuild/etc? 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] pg_stat_reset() weirdness
Tom Lane wrote: > Unfortunately I don't believe Joe's theory --- an OID conflict between > pg_proc and pg_type shouldn't matter, and in any case the particular > sanity check that's failing is not looking at pg_type: I guess I should know better than to jump to a conclusion. But I *was* under the impression we were supposed to use the unused_oids script to get a unique oid for a new function. > -- Look for illegal values in pg_proc fields. > -- NOTE: currently there are a few pg_proc entries that have prorettype = 0. > -- Someday that ought to be cleaned up. > SELECT p1.oid, p1.proname > FROM pg_proc as p1 > WHERE (p1.prolang = 0 OR p1.prorettype = 0 OR >p1.pronargs < 0 OR p1.pronargs > 16) > AND p1.proname !~ '^pl[^_]+_call_handler$' > AND p1.proname !~ '^RI_FKey_' > AND p1.proname !~ 'costestimate$' > AND p1.proname != 'update_pg_pwd_and_pg_group'; > > The pg_stat_reset definition I see in Chris' "round 3" patch does not > look like it should trigger this test. (I had misremembered the > previous discussion to think that he'd set prorettype = 0, but he > didn't.) So what's going wrong exactly? It needs investigation. > Actually, I don't see the regression failure here at all, now that I try the patch. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
On Sat, 2002-08-10 at 00:25, Mark Kirkwood wrote: > Ralph Graulich wrote: > > >Hi, > > > >just my two cents worth: I like having the files sized in a way I can > >handle them easily with any UNIX tool on nearly any system. No matter > >wether I want to cp, tar, dump, dd, cat or gzip the file: Just keep it at > >a maximum size below any limits, handy for handling. > > > Good point... however I was thinking that being able to dump the entire > database without resporting to "gzips and splits" was handy... > > > > >For example, Oracle suggests it somewhere in their documentation, to keep > >datafiles at a reasonable size, e.g. 1 GB. Seems right to me, never had > >any problems with it. > > > Yep, fixed or controlled sizes for data files is great... I was thinking > about databases rather than data files (altho I may not have made that > clear in my mail) > I'm actually amazed that postgres isn't already using large file support. Especially for tools like dump. I do recognize the need to keep files manageable in size but my file sizes for my needs may differ from your sizing needs. Seems like it would be a good thing to enable and simply make it a function for the DBA to handle. After all, even if I'm trying to keep my dumps at around 1GB, I probably would be okay with a dump of 1.1GB too. To me, that just seems more flexible. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] pg_stat_reset() weirdness
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Ah doh - I thought it was catching it returning a boolean. I'll fix and > resubmit. Unfortunately I don't believe Joe's theory --- an OID conflict between pg_proc and pg_type shouldn't matter, and in any case the particular sanity check that's failing is not looking at pg_type: -- Look for illegal values in pg_proc fields. -- NOTE: currently there are a few pg_proc entries that have prorettype = 0. -- Someday that ought to be cleaned up. SELECT p1.oid, p1.proname FROM pg_proc as p1 WHERE (p1.prolang = 0 OR p1.prorettype = 0 OR p1.pronargs < 0 OR p1.pronargs > 16) AND p1.proname !~ '^pl[^_]+_call_handler$' AND p1.proname !~ '^RI_FKey_' AND p1.proname !~ 'costestimate$' AND p1.proname != 'update_pg_pwd_and_pg_group'; The pg_stat_reset definition I see in Chris' "round 3" patch does not look like it should trigger this test. (I had misremembered the previous discussion to think that he'd set prorettype = 0, but he didn't.) So what's going wrong exactly? It needs investigation. 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] Proposal: stand-alone composite types
> than to do: > > CREATE TYPE some_arbitrary_name AS (f1 int, f2 text); > CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name; > > But I admit it is only a "nice-to-have", not a "need-to-have". > > How do others feel? Do we want to be able to implicitly create a > composite type during function creation? Or is it unneeded bloat? > > I prefer the former, but don't have a strong argument against the latter. The former is super sweet, but does require some extra catalog entries for every procedure - but that's the DBA's problem. They can always use the latter syntax. The format syntax is cool and easy and it Should Just Work for newbies... Chris ---(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_stat_reset() weirdness
Ah doh - I thought it was catching it returning a boolean. I'll fix and resubmit. Chris - Original Message - From: "Joe Conway" <[EMAIL PROTECTED]> To: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> Cc: "Hackers" <[EMAIL PROTECTED]> Sent: Friday, August 09, 2002 11:26 PM Subject: Re: [HACKERS] pg_stat_reset() weirdness > Christopher Kings-Lynne wrote: > > Hi guys, > > > > If you apply the pg_stat_reset() function patch you get this regression > > failure. Is this because it's returning a bool I guess? Shall I just fix > > the regression test to exclude this function? > > > > AND p1.proname != 'update_pg_pwd_and_pg_group'; > >oid |proname > > ! --+--- > > ! 2249 | pg_stat_reset > > ! (1 row) > > Likely because this is now in CVS: > > DATA(insert OID = 2249 ( recordPGNSP PGUID 4 t p t \054 0 0 > oidin oidout i p f 0 -1 0 _null_ _null_ )); > #define RECORDOID 2249 > > The Oids conflict. > > Joe > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_stat_reset() weirdness
Hang on - I _can't_ fix the function defiition - it returns a bool and that's why it's failing. I can't have it returning a void because it's not possible. Check list of all other excluded functions as well... Chris - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> Cc: "Hackers" <[EMAIL PROTECTED]> Sent: Friday, August 09, 2002 9:50 PM Subject: Re: [HACKERS] pg_stat_reset() weirdness > "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > If you apply the pg_stat_reset() function patch you get this regression > > failure. Is this because it's returning a bool I guess? Shall I just fix > > the regression test to exclude this function? > > No, you should fix the function definition. The sanity checks are there > for a reason. > > 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
