Re: [HACKERS] incompatible changes of PQsetdbLogin()
> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > It seems some incompatible changes have been made between 7.0 and
> > current. In 7.0, if a parameter is NULL OR a null string (""), then
> > the value from an environment variable is applied. However in current
> > ONLY NULL is considered. Is there any reason for this?
>
> Peter E. did that recently, after discussion that concluded it was a
> good idea --- otherwise there is no way to override an environment
> variable with an empty string. Do you have an example where it's
> a bad idea?
For PGHOST Peter E.'s changes seem reasonable. But what about PGPORT?
In 7.0.x, if pgport is an empty string and PGPORT environment variable
is not set, then the default port no. (5432) is used. However, in
current, if pgport is an empty string, then the empty string is
assumed as a port no. that causes a failure on connection even if
PGPORT variable is set.
--
Tatsuo Ishii
[HACKERS] howto: install posgresql on darwin/osxpb
72L;144L;216L;288L;360L;0L;Here's a patch to get postgresql up and running (I've only tested it on PB w/ dev tools but it should work on Darwin as well). It's very rough but I thought I'd put it up for those of you who are interested. If anyone has suggestions or help for some of the remaining issues listed below, please email me. Thanks.
http://www.gerga.com/pg_darwin_patch.tar.gz
to install:
wget ftp://ftp.postgresql.org/pub/v7.0.2/postgresql-7.0.2.tar.gz
wget http://www.gerga.com/pg_darwin_patch.tar.gz
tar xzvf postgresql-7.0.2.tar.gz
tar xzvf pg_darwin_patch.tar.gz
cd postgresql-7.0.2
patch -p1 < ../pg_darwin_patch
(WARNING! You need to put a symlink in your /usr/include/sys dir to sem.h. OSXPB doesn't have the sysv semaphore implementation included but it seems to be in the newer kernels -- so check before you clobber something you might already have. I'm currently just using the qnx semaphore port but this should probably be removed when Apple's semaphore implementation arrives.)
ln -s `pwd`/src/backend/port/darwin/sem.h /usr/include/sys/sem.h
cd src
./configure --without-CXX -with-perl
(c++ lib needs to be hacked; perl seems to work; I haven't really tried other options yet.)
make
make install
these default paths help:
export DYLD_LIBRARY_PATH="${DYLD_LIBRARY_PATH}:/usr/local/pgsql/lib"
export PGDATA="/usr/local/pgsql/data"
export PGLIB="/usr/local/pgsql/lib"
read postgresql-7.0.2/INSTALL for setting up the postmaster and getting things going.
(initdb /usr/local/pgsql/data; postmaster)
for the regression tests:
cd postgres-7.0.2/src/tests/regress
make
make runtest
(int2 & int4 will "fail" because of different error messages)
(geometry "fails" because 0 != -0 for some reason)
some issues/problems:
- if you try to load a badly made dynamic library, you will get an error like:
dyld: /usr/local/pgsql/bin/./postmaster Undefined symbols:
dyld_stub_binding_helper
Server process (pid 8932) exited with status 17152 at Sun Oct 22 00:41:22 2000
Terminating any active server processes...
Server processes were terminated at Sun Oct 22 00:41:22 2000
Reinitializing shared memory and semaphores
Need to fix/make the error routine in src/backend/port/dynloader/darwin.c?
-running the regression tests more than once seems to corrupt the database. it can't be dropped or created the second time. I I haven't found a way to get rid of it beside re-initdb-ing the data directory which isn't a very useful solution at all. ideas/suggestions?
- there may be semaphore/shared memory leaking going on. the routines are a bit of a cludge and the postgres ipcclean utility needs ipcs. is there an osx equivalent?
- when compiled with any -O, the following regression tests fail:
oidjoins .. failed
type_sanity .. failed
opr_sanity .. failed
28L;56L;84L;112L;140L;168L;196L;224L;252L;280L;308L;336L;
Re: [HACKERS] howto: install posgresql on darwin/osxpb
Bruce Hartzler <[EMAIL PROTECTED]> writes: > (int2 & int4 will "fail" because of different error messages) > (geometry "fails" because 0 != -0 for some reason) See the documentation about platform-specific regress test comparison files. You'll need to add entries to regress/resultmap. There may well be existing comparison files that work for you, else add another one. > - when compiled with any -O, the following regression tests fail: > oidjoins .. failed > type_sanity .. failed > opr_sanity .. failed Probably the known fmgr portability problems for PPC. Postgres has never worked with -O on any PPC platform (at least not with gcc). It's actually not so interesting to be porting 7.0.* at this point --- what you really should be working on is pgsql's current development sources, with the hope that you can contribute patches to make 7.1 work out-of-the-box. The fmgr issues are fixed now on PPC (I've run regression tests -O2 on LinuxPPC with no problems), and Peter Eisentraut has made huge strides toward bringing our configure/build system into line with standard GNU & autoconf practice. So in theory the port should be less painful than it was for 7.0.*. You can pull current sources from our CVS server, or there's a nightly snapshot tarfile. Note you should test on a known-supported platform (LinuxPPC is now in that class, if that's convenient for you) to make sure you got a working snapshot and not momentary devel breakage :-( regards, tom lane
Re: [HACKERS] Problem do backup/restore for empty database
At 18:33 21/10/00 +0700, Denis Perchine wrote: > >pg_dump --blob -Fc test3 -f test3.tar -v >pg_restore test3.tar --db=test4 -v > Should work with current CVS sources now... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] is there a way to DROP foreign key constraint ?
Stephan Szabo wrote: > > On Fri, 20 Oct 2000, Hannu Krosing wrote: > > > I'm unable to find the complementary function to > > > > ALTER TABLE t ADD FOREIGN KEY(id) REFERENCES pkt(pk); > > Currently, ALTER TABLE ... DROP table constraint definition > doesn't exist. > > > I would try DROP TRIGGER, but I've also been unable to > > find a way to name the constraint ;( > > Umm, put the constraint name in the table constraint definition? > >From the SQL spec: > ::= > [ ] >[ ] > ::= CONSTRAINT Thanks! I was missing the CONSTRAINT word in Hannu
[HACKERS] AnonCVS access?
Ok, I can't find it on the web site How do I check out the current tree? (I want to play with Peter_E's changes...) LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] AnonCVS access?
Larry Rosenman <[EMAIL PROTECTED]> writes: > How do I check out the current tree? Up-to-date info is in http://www.postgresql.org/devel-corner/docs/postgres/anoncvs.htm (Hey Bruce, is this in the Developer's FAQ?) regards, tom lane
Re: [HACKERS] AnonCVS access?
I couldn't find a link ANYWHERE on the site to this file. There are hints about it's existence, but it ain't linked obviously anywhere... Thanks! LER * Tom Lane <[EMAIL PROTECTED]> [001022 18:44]: > Larry Rosenman <[EMAIL PROTECTED]> writes: > > How do I check out the current tree? > > Up-to-date info is in > > http://www.postgresql.org/devel-corner/docs/postgres/anoncvs.htm > > (Hey Bruce, is this in the Developer's FAQ?) > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] pg_dump of regression (again)
Philip Warner <[EMAIL PROTECTED]> writes: >> So you have to give the raw type name, no fancy fandangoes ... > OK - I'll use typname in CREATE AGGREGATE, and see how it hangs together. > Do you know if the type parser is invoked in function declarations? If not > I probably just need to limit use of format_type to table declarations. BTW, type parsing is now done "properly" in CREATE FUNCTION, CREATE AGGREGATE, etc, so you should be able to use format_type more freely now. regards, tom lane
[HACKERS] GCC: Works fine for me...
Shared libpq works for me. I bet you were getting tripped up by some ENV vars I set globally... LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 PGP signature
[HACKERS] Re: Linking
I've already cc'd PeterE. I suspect we want the -lpq build to have -lsocket (at least on THIS (unixware) platform. Larry * KuroiNeko <[EMAIL PROTECTED]> [001022 19:25]: > > Well, all in all, adding -lsocket is just enough. I was trying to compile > /home/ed/t.c, which contains just PQconnectdb() and PQfinish(). > > $ cc t.c -o t -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lpq > > The above fails with undefined symbols: > > Undefined first referenced > symbol in file > inet_aton libpq.so > gethostbyname libpq.so > UX:ld: ERROR: Symbol referencing errors. No output written to t > > Adding -lsocket will make it compile. I mean this is _probably_ not a big > deal, but feels abit inconsistent. After all, t.c itself calls nothing from > -lsocket > Of course, the final decision should be made by maintainers, but I can't > help feeling this issue needs to be put up, or at least registered in your > records. > I'll try building .so and let you know. > > Thx > > Ed > > > -- > > contaminated fish and microchips > huge supertankers on Arabian trips > oily propaganda from the leaders' lips > all about the future > there's people over here, people over there > everybody's looking for a little more air > crossing all the borders just to take their share > planning for the future > > Rainbow, Difficult to Cure -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] AnonCVS access?
No. I will add a mention to look on the programmer's manual for that information. > Larry Rosenman <[EMAIL PROTECTED]> writes: > > How do I check out the current tree? > > Up-to-date info is in > > http://www.postgresql.org/devel-corner/docs/postgres/anoncvs.htm > > (Hey Bruce, is this in the Developer's FAQ?) > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] AnonCVS access?
On Sun, 22 Oct 2000, Bruce Momjian wrote: > No. I will add a mention to look on the programmer's manual for that > information. Where is it in the programmer's manual? Vince. > > > Larry Rosenman <[EMAIL PROTECTED]> writes: > > > How do I check out the current tree? > > > > Up-to-date info is in > > > > http://www.postgresql.org/devel-corner/docs/postgres/anoncvs.htm > > > > (Hey Bruce, is this in the Developer's FAQ?) > > > > regards, tom lane > > > > > -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
Re: [HACKERS] AnonCVS access?
I thought it was there? > On Sun, 22 Oct 2000, Bruce Momjian wrote: > > > No. I will add a mention to look on the programmer's manual for that > > information. > > Where is it in the programmer's manual? > > Vince. > > > > > > Larry Rosenman <[EMAIL PROTECTED]> writes: > > > > How do I check out the current tree? > > > > > > Up-to-date info is in > > > > > > http://www.postgresql.org/devel-corner/docs/postgres/anoncvs.htm > > > > > > (Hey Bruce, is this in the Developer's FAQ?) > > > > > > regards, tom lane > > > > > > > > > > > -- > == > Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://www.cloudninegifts.com > == > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] AnonCVS access?
Vince Vielhaber <[EMAIL PROTECTED]> writes: > Where is it in the programmer's manual? It's in the appendices of the developer's guide. regards, tom lane
Re: [HACKERS] AnonCVS access?
On Sun, 22 Oct 2000, Tom Lane wrote: > Vince Vielhaber <[EMAIL PROTECTED]> writes: > > Where is it in the programmer's manual? > > It's in the appendices of the developer's guide. What developer's guide? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
Re: [HACKERS] AnonCVS access?
Vince Vielhaber <[EMAIL PROTECTED]> writes: >> It's in the appendices of the developer's guide. > What developer's guide? http://www.postgresql.org/devel-corner/docs/postgres/ has a developer's guide link ... regards, tom lane
Re: [HACKERS] AnonCVS access?
On Sun, 22 Oct 2000, Tom Lane wrote: > Vince Vielhaber <[EMAIL PROTECTED]> writes: > >> It's in the appendices of the developer's guide. > > > What developer's guide? > > http://www.postgresql.org/devel-corner/docs/postgres/ > has a developer's guide link ... Hiding in plain sight. I must have looked at it 5 times and never even saw it. Must be time for bed! Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
[HACKERS] relation ### modified while in use
I'm having the error 'relation modified while in use' fairly often. It is the same relation that's always giving a problem. Usually after all currently-running backends die away with that error, error disappears. If I shutdown, ipcclean, start up postgres, it also disappears. What causes this? I'm having a feeling that it has to do with referential integrity (the table in question is referenced by almost every other table), and with [possibly] a leak of reference counts? This is all with pg7.0.2 on i386. -alex
[HACKERS] Holes in the install process
Having just installed a few times, and being new to it, I've fallen in some holes the rest of you may not notice. For one thing, has anybody recently read the stuff that prints at the end of a 'make install'? It gives three or so pointers to pages at postgresql.org, which pages do not exist, or at least cannot be reached as described. For another the INSTALL document is a bit coy about what happens when you install as root. If you have Perl configured, the process creates at least one file that will cause problems if you later try a 'make' as non-root. You won't have permission to proceed. And I don't like to be root more than I have to, so I don't like this. Moreover, I think the INSTALL instructions should be a bit more careful about the bits where you have to be the Postgress root rather than your usual user name. I found it a bit frustrating going through the dump/restore process till I figured that out. Finally, there may be a real bug in the dumpall/restore business. I had a database created with a version I got from some RPM packages somewhere. I don't remember where, and I surely didn't know how they were configured. When I built a new version, I did NOT configure multibyte support. This caused restore to fail because one of the very early commands was setting the US-ASCII encoding. PostgreSQL barked at me that multibyte was not supported, and the process stopped. This seems extreme, since I'm guessing that no multibyte means I'm locked in US-ASCII. In any event, this seems an unnecessary gotcha for the inexperienced, and it may mean that I'm locked into multibyte suppport that I don't want or need. -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead
Re: [HACKERS] failed runcheck
Did you run make distclean? I've run regtests before committing changes.
Vadim
- Original Message -
From: "Patrick Welche" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, October 21, 2000 10:17 AM
Subject: [HACKERS] failed runcheck
> First a core dump which can be relieved by:
>
> Index: catalog.c
> ===
> RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/backend/catalog/catalog.c,v
> retrieving revision 1.34
> diff -c -u -r1.34 catalog.c
> --- catalog.c 2000/10/16 14:52:02 1.34
> +++ catalog.c 2000/10/21 17:07:09
> @@ -173,7 +173,7 @@
> bool
> IsSystemRelationName(const char *relname)
> {
> - if (relname[0] && relname[1] && relname[2])
> + if (relname && relname[0] && relname[1] && relname[2])
> return (relname[0] == 'p' &&
> relname[1] == 'g' &&
> relname[2] == '_');
>
> (symptoms at the end of message)
>
> But now the bit I don't see how to solve: the regression postmaster
doesn't
> startup because it can't find tmp_check/data/base/1/1259. The only files I
> see are 1/{1255,PG_VERSION}. Where does 1259 come from?
>
> Cheers,
>
> Patrick
>
>
> #0 IsSystemRelationName (relname=0x0) at catalog.c:176
> #1 0x807ed9a in IsSharedSystemRelationName (relname=0x0) at catalog.c:197
> #2 0x80e9272 in RelationInitLockInfo (relation=0x82af018) at lmgr.c:119
> #3 0x81202ef in formrdesc (relationName=0x816ad7e "pg_class", natts=22,
> att=0x8173600) at relcache.c:1193
> #4 0x8120c12 in RelationCacheInitialize () at relcache.c:1953
> #5 0x81266b3 in InitPostgres (dbname=0xbfbfd666 "template1",
username=0x0)
> at postinit.c:329
> #6 0x807dde0 in BootstrapMain (argc=7, argv=0xbfbfd510) at
bootstrap.c:358
> #7 0x80bc67c in main (argc=8, argv=0xbfbfd50c) at main.c:119
> #8 0x806367e in ___start ()
> (gdb) print *relation
> $3 = {rd_fd = -1, rd_nblocks = 0, rd_refcnt = 0, rd_myxactonly = 0 '\000',
> rd_isnailed = 0 '\000', rd_unlinked = 0 '\000', rd_indexfound = 0
'\000',
> rd_uniqueindex = 0 '\000', rd_am = 0x101, rd_rel = 0x0, rd_id = 0,
> rd_indexlist = 0x82af0a0, rd_lockInfo = {lockRelId = {relId = 0, dbId =
0}},
> rd_att = 0x0, rd_rules = 0x0, rd_rulescxt = 0x82af128, rd_istrat = 0x0,
> rd_support = 0x0, trigdesc = 0x0}
>
Re: [HACKERS] relation ### modified while in use
Alex Pilosov <[EMAIL PROTECTED]> writes: > I'm having the error 'relation modified while in use' fairly > often. It is the same relation that's always giving a problem. Hmm, could we see the full schema dump for that relation? (pg_dump -s -t tablename dbname will do) If you are not actively modifying the schema, then in theory you should not see this message, but... regards, tom lane
Re: [HACKERS] relation ### modified while in use
I think this happens after I create/modify tables which reference this
table. This is spontaneous, and doesn't _always_ happen...
Anything I could do next time it craps up to help track the problem down?
-alex
CREATE TABLE "customers" (
"cust_id" int4 DEFAULT nextval('customers_cust_id_seq'::text) NOT
NULL,
"phone_npa" character(3) NOT NULL,
"phone_nxx" character(3) NOT NULL,
"phone_rest" character(4) NOT NULL,
"e_mail" character varying(30),
"daytime_npa" character(3),
"daytime_nxx" character(3),
"daytime_rest" character(4),
"is_business" bool DEFAULT 'f' NOT NULL,
PRIMARY KEY ("cust_id") );
CREATE CONSTRAINT TRIGGER "" AFTER DELETE ON "customers" NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del" ('', 'cc_charges', 'customers',
'UNSPECIFIED', 'cust_id', 'cust_id');
CREATE CONSTRAINT TRIGGER "" AFTER UPDATE ON "customers" NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd" ('', 'cc_charges', 'customers',
'UNSPECIFIED', 'cust_id', 'cust_id');
On Sun, 22 Oct 2000, Tom Lane wrote:
> Alex Pilosov <[EMAIL PROTECTED]> writes:
> > I'm having the error 'relation modified while in use' fairly
> > often. It is the same relation that's always giving a problem.
>
> Hmm, could we see the full schema dump for that relation?
> (pg_dump -s -t tablename dbname will do)
>
> If you are not actively modifying the schema, then in theory you should
> not see this message, but...
>
> regards, tom lane
>
>
Re: [HACKERS] Holes in the install process
"Kevin O'Gorman" <[EMAIL PROTECTED]> writes: > For one thing, has anybody recently read the stuff that prints at the > end of a 'make install'? Yeah, it's pretty out-of-date. Someone or other had promised to update it (Peter E. I think). > For another the INSTALL document is a bit coy about what happens when > you install as root. If you have Perl configured, the process creates > at least one file that will cause problems if you later try a 'make' > as non-root. "At least one file" isn't very helpful. If you want these things fixed, how about *specifics*? Patches would be even better ;-). (The same goes for documentation shortcomings, btw.) > I did NOT configure multibyte support. This caused restore to fail > because one of the very early commands was setting the US-ASCII > encoding. PostgreSQL barked at me that multibyte was not supported, > and the process stopped. Hmm, I suppose CREATE DATABASE WITH ENCODING 'US-ASCII' had better be accepted even when multibyte isn't enabled. (Tatsuo, any comment here?) regards, tom lane
[HACKERS] Re: Navigating time-warps in the CVS tree (was re the rule system)
"Kevin O'Gorman" <[EMAIL PROTECTED]> writes: > It's odd. I had already tried "8 Oct 2000 10:00:00 PDT" on one system > (RedHat Linux 6.1), and it had worked. Today I'm building on a > Caldera 2.3 system, and both the 00:00 and 10:00 builds fail. Hm. Portability bug maybe? But I can't tell with no info. > I've attached the output of the make. Uh, it looked more like an amazon.com search from here... regards, tom lane
Re: AW: [HACKERS] The lightbulb just went on...
On Thu, 19 Oct 2000, Tom Lane wrote: > Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes: > >> SELECT session_data, id > >> FROM sessions > >> WHERE id = ? > >> FOR UPDATE > >> > >> I think part of my problem might be that sessions is a view > >> and not a table, > > > Did you create an on update do instead rule ? Yes actually :). But Ive since elimintated the rule and figured out I could get the equivalent functionality I was getting the the RULE/VIEW by just using a simple PL/pgSQL trigger. Since doing that, the "relation X modified while in use" errors have gone away, but I'm still not sure I trust VACUUM ANALYZE enough to run it on a non-idle production database :). I want to do more testing before I get that brave :). Mike
Re: [HACKERS] relation ### modified while in use
Alex Pilosov <[EMAIL PROTECTED]> writes: > I think this happens after I create/modify tables which reference this > table. This is spontaneous, and doesn't _always_ happen... Um. I was hoping it was something more easily fixable :-(. What's causing the relcache to decide that the rel has been modified is the addition or removal of foreign-key triggers on the rel. Which seems legitimate. (It's barely possible that we could get away with allowing triggers to be added or deleted mid-transaction, but that doesn't feel right to me.) There are two distinct known bugs that allow the error to be reported. These have been discussed before, but to recap: 1. relcache will complain if the notification of cache invalidation arrives after transaction start and before first use of the referenced rel (when there was already a relcache entry left over from a prior transaction). In this situation we should allow the change to occur without complaint, ISTM. But the relcache doesn't currently have any concept of first reference versus later references. 2. Even with #1 fixed, you could still get this error, because we are way too willing to release locks on rels that have been referenced. Therefore you can get this sequence: Session 1 Session 2 begin; select * from foo; -- LockRelation(AccessShareLock); -- UnLockRelation(AccessShareLock); ALTER foo ADD CONSTRAINT; -- LockRelation(AccessExclusiveLock); -- lock released at commit select * from foo; -- LockRelation(AccessShareLock); -- table schema update is detected, error must be reported I think that we should hold at least AccessShareLock on any relation that a transaction has touched, all the way to end of transaction. This creates the potential for deadlocks that did not use to happen; for example, if we have two transactions that concurrently both do begin; select * from foo; -- gets AccessShareLock LOCK TABLE foo; -- gets AccessExclusiveLock ... end; this will work currently because the SELECT releases AccessShareLock when done, but it will deadlock if SELECT does not release that lock. That's annoying but I see no way around it, if we are to allow concurrent transactions to do schema modifications of tables that other transactions are using. Comments anyone? regards, tom lane
Re: [HACKERS] relation ### modified while in use
On Mon, 23 Oct 2000, Tom Lane wrote: > when done, but it will deadlock if SELECT does not release that lock. > > That's annoying but I see no way around it, if we are to allow > concurrent transactions to do schema modifications of tables that other > transactions are using. I might be in above my head, but maybe this is time for yet another type of lock? "Do-not-modify-this-table-under-me" lock, which shall persist until transaction commits, and will conflict only with alter table lock/AccessExclusiveLock? I realise we have already many lock types, but this seems to be proper solution to me... In related vein: Is there a way to see who (at least process id) is holding locks on tables?
Re: [HACKERS] relation ### modified while in use
Alex Pilosov <[EMAIL PROTECTED]> writes: > I might be in above my head, but maybe this is time for yet another type > of lock? Wouldn't help --- it's still a deadlock. regards, tom lane
Re: [HACKERS] relation ### modified while in use
On Mon, 23 Oct 2000, Alex Pilosov wrote: > On Mon, 23 Oct 2000, Tom Lane wrote: > > > when done, but it will deadlock if SELECT does not release that lock. > > > > That's annoying but I see no way around it, if we are to allow > > concurrent transactions to do schema modifications of tables that other > > transactions are using. > > I might be in above my head, but maybe this is time for yet another type > of lock? "Do-not-modify-this-table-under-me" lock, which shall persist > until transaction commits, and will conflict only with alter table > lock/AccessExclusiveLock? I just realised that I _am_ in above my head, and the above makes no sense, and is identical to holding AccessShareLock. Sorry ;) -alex
Re: [HACKERS] relation ### modified while in use
On Mon, 23 Oct 2000, Tom Lane wrote: > begin; > select * from foo; -- gets AccessShareLock > LOCK TABLE foo; -- gets AccessExclusiveLock > ... > end; > > this will work currently because the SELECT releases AccessShareLock > when done, but it will deadlock if SELECT does not release that lock. Probably a silly question, but since this is the same transaction, couldn't the lock be 'upgraded' without a problem? Or postgres doesn't currently have idea of lock upgrades...? -alex
Re: [HACKERS] relation ### modified while in use
Alex Pilosov <[EMAIL PROTECTED]> writes: > On Mon, 23 Oct 2000, Tom Lane wrote: >> begin; >> select * from foo; -- gets AccessShareLock >> LOCK TABLE foo; -- gets AccessExclusiveLock >> ... >> end; >> >> this will work currently because the SELECT releases AccessShareLock >> when done, but it will deadlock if SELECT does not release that lock. > Probably a silly question, but since this is the same transaction, > couldn't the lock be 'upgraded' without a problem? No, the problem happens when two transactions do the above at about the same time. After the SELECTs, both transactions are holding AccessShareLock, and both are waiting for the other to let go so's they can get AccessExclusiveLock. AFAIK any concept of "lock upgrade" falls afoul of this basic deadlock risk. We do have a need to be careful that the system doesn't try to do lock upgrades internally. For example, in LOCK TABLE foo; the parsing step had better not grab AccessShareLock on foo in advance of the main execution step asking for AccessExclusiveLock. regards, tom lane
Re: [HACKERS] relation ### modified while in use
At 01:01 23/10/00 -0400, Tom Lane wrote: >(It's barely possible that we could get away with allowing >triggers to be added or deleted mid-transaction, but that doesn't feel >right to me.) > A little OT, but the above is a useful feature for managing data; it's not common, but the following sequence is essential to managing a database safely: - Start TX - Drop a few triggers, constraints etc - Add/change data to fix erroneous/no longer accurate business rules (audited, of course) - Reapply the triggers, constraints - Make sure it looks right - Commit/Rollback based on the above check It is very undesirable to drop the triggers/constraints in a separate transaction since a communications failure could leave them unapplied. At least in one TX, the recovery process should back out the TX. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] relation ### modified while in use
Philip Warner <[EMAIL PROTECTED]> writes: > At 01:01 23/10/00 -0400, Tom Lane wrote: >> (It's barely possible that we could get away with allowing >> triggers to be added or deleted mid-transaction, but that doesn't feel >> right to me.) > A little OT, but the above is a useful feature for managing data; it's not > common, but the following sequence is essential to managing a database safely: > - Start TX > - Drop a few triggers, constraints etc > - Add/change data to fix erroneous/no longer accurate business rules > (audited, of course) > - Reapply the triggers, constraints > - Make sure it looks right > - Commit/Rollback based on the above check There is nothing wrong with the above as long as you hold exclusive lock on the tables being modified for the duration of the transaction. The scenario I'm worried about is on the other side, ie, a transaction that has already done some things to a table is notified of a change to that table's triggers/constraints/etc being committed by another transaction. Can it deal with that consistently? I don't think it can in general. What I'm proposing is that once an xact has touched a table, other xacts should not be able to apply schema updates to that table until the first xact commits. regards, tom lane
Re: [HACKERS] relation ### modified while in use
At 01:37 23/10/00 -0400, Tom Lane wrote: > >What I'm proposing is that once an xact has touched a >table, other xacts should not be able to apply schema updates to that >table until the first xact commits. Totally agree. You may want to go further and say that metadata changes can not be made while that *connection* exists: if the client has prepared a query against a table will it cause a problem when the query is run? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] relation ### modified while in use
Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > > At 01:01 23/10/00 -0400, Tom Lane wrote: > >> (It's barely possible that we could get away with allowing > >> triggers to be added or deleted mid-transaction, but that doesn't feel > >> right to me.) > > > A little OT, but the above is a useful feature for managing data; it's not > > common, but the following sequence is essential to managing a database safely: > > > - Start TX > > - Drop a few triggers, constraints etc > > - Add/change data to fix erroneous/no longer accurate business rules > > (audited, of course) > > - Reapply the triggers, constraints > > - Make sure it looks right > > - Commit/Rollback based on the above check > > There is nothing wrong with the above as long as you hold exclusive > lock on the tables being modified for the duration of the transaction. > > The scenario I'm worried about is on the other side, ie, a transaction > that has already done some things to a table is notified of a change to > that table's triggers/constraints/etc being committed by another > transaction. Can it deal with that consistently? I don't think it can > in general. What I'm proposing is that once an xact has touched a > table, other xacts should not be able to apply schema updates to that > table until the first xact commits. > I agree with you. I've wondered why AccessShareLock is a short term lock. If we have a mechanism to acquire a share lock on a tuple,we could use it for managing system info generally. However the only allowed lock on a tuple is exclusive. Access(Share/Exclusive) Lock on tables would give us a restricted solution about pg_class tuples. Thers'a possibility of deadlock in any case but there are few cases when AccessExclusiveLock is really needed and we could acquire an AccessExclusiveLock manually from the first if necessary. I'm not sure about the use of AccessShareLock in parse-analyze- optimize phase however. Regards. Hiroshi Inoue
[HACKERS] Re: [COMMITTERS] pgsql/src/test/regress/expected (plpgsql.out inet.out foreign_key.out errors.out)
> Modified Files: ... > Some small polishing of Mark Hollomon's cleanup of DROP command: might > as well allow DROP multiple INDEX, RULE, TYPE as well. Add missing > CommandCounterIncrement to DROP loop, which could cause trouble otherwise > with multiple DROP of items affecting same catalog entries. Try to > bring a little consistency to various error messages using 'does not exist', > 'nonexistent', etc --- I standardized on 'does not exist' since that's > what the vast majority of the existing uses seem to be. Good idea(s). Thanks for cleaning up the error messages... - Thomas
