Re: [HACKERS] Disaster!
Randolf Richardson [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Greg Stark) stated in comp.databases.postgresql.hackers: The traditional Unix filesystems certainly don't return errors at close. Why shouldn't the close() function return an error? If an invalid file handle was passed to it, it most certainly should indicate this Of course. We're discussing the situation where no errors were reported in prior syscalls --- in particular, open() succeeded. 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
Re: [HACKERS] Disaster!
Manfred Spraul [EMAIL PROTECTED] writes: The checkpoint code uses sync() right now. Actually sync();sleep(2);sync(). Win32 has no sync() call, therefore it will use fsyncs. Perhaps platforms with deferred errors on close must use fsync, too. Hopefully parallel fsyncs - sequential fsyncs could be slow due to more seeking. That code is known to be totally bogus in theory. However in practice it seems to be the best of the possible bad choices. Even on filesystems where errors won't be deferred after the write() the data is still not guaranteed to be on disk. Even after the sync() call. There's no guarantee of any particular sleep time being enough. This was brought up a few months ago. The only safe implementation would be to fsync every file descriptor that had received writes. The problem is keeping track of which file descriptors those are. Also people were uncertain whether a backend opening a file and calling fsync would guarantee that writes written to the same file by other processes through other file descriptors would be flushed. I'm fairly convinced they would be on all sane vfs implementations but others were less convinced. -- greg ---(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] Can not always connect to postmaster. Sometimes get Connection refused.
Hi, Were running postgres7.3 on RedHat7.3. The Postgres at boot time starts up from a java (1.2) class that runs the following commands in order: /usr/local/pgsql/bin/pg_ctl -w start -D /var/lib/pgsql -l /var/log/pgsqld.log -o i /usr/local/pgsql/bin/createdb management . . . As you can tell the postmaster starts up with -i option here. ++ In pg_hba.conf we have: local all all trust host all all 127.0.0.1 255.255.255.255 trust host all all 192.168.0.0 255.255.0.0 trust The host IP address is 192.168.55.5 which is in the right subnet. + In postgresql.conf: # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 64 #superuser_reserved_connections = 2 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal tcpip_socket = true ++ Sometimes, after Postgres starts up, when the database clients try to access the db from the localhost, we get the following error: com.solarmetric.kodo.Runtime - com.solarmetric.kodo.runtime.FatalDataStoreException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. a) Any ideas whats causing postmaster NOT to accept connections some of the time? This problem almost always goes away upon rebooting the machine. Any help is much appreciated. Thanks, -Allen **This email message and any files transmitted with it contain confidential information intended only for the person(s) to whom this email message is addressed. If you have received this email message in error, please notify the sender immediately by telephone or email and destroy the original message without making a copy. Thank you.**
Re: [HACKERS] Named arguments in function calls
I agree with Tom on this. Good operator combinations are hard to find when you are creating new operators. = is a particularly good one. Barring any override from the SQL200x standard, I would strongly suggest AS, too. elein [EMAIL PROTECTED] On Sun, Jan 25, 2004 at 02:54:12PM -0500, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: I've been looking (and coded) a little bit on named function calls. Calls on the form: foo (x = 13, y = 42) Implementing this means that the symbol = no longer can be defined by the user as an operator. It's not used as default in pg, but I just want to tell you up front in case you don't like that. Is it really necessary to steal it? There's some precedent for special cases in argument lists: , is an operator in C yet it has special meaning in function arguments. I'm not happy with the concept of reserved operator names, either. I think a little more work ought to be put into the grammar to see if we can match Oracle's syntax without reserving the operator, and if we can't, choose a different syntax using a keyword instead of an operator. One that comes to mind immediately is AS: foo (13 as x, 42 as y) AS is already a fully reserved word, so this wouldn't break any existing applications. Furthermore it seems to fit more naturally with SQL syntax in general --- you could see this as equivalent to the column renaming that AS does in a SELECT list. I've never been impressed with the concept of copying Oracle just because they're Oracle. This seems like a case where they've chosen an unfortunate syntax that we should not break things to emulate. BTW, has anyone looked to see whether SQL 200x has pre-empted this decision yet? 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]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Undefined symbol elog
Hi folks, I upgraded two servers from 7.3.* to 7.4.1. In both cases, the pgcrypto functions failed to migrate.. I used pg_dumpall. This is the cause of the problem: freshports=# CREATE FUNCTION digest (text, text) RETURNS bytea freshports-# AS '$libdir/pgcrypto', 'pg_digest' freshports-# LANGUAGE c; ERROR: could not load library /usr/local/lib/postgresql/pgcrypto.so: dlopen '/usr/local/lib/postgresql/pgcrypto.so' failed. (/usr/local/lib/postgresql/pgcrypto.so: Undefined symbol elog) Recompiling and installing contrib/pgcrypto made the problem go away. I found no mention of this problem in the archives. FWIW: I was using the FreeBSD port on 4.9-STABLE. -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/ ---(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] Undefined symbol elog
My search was wrong. This is a known issue. Sorry for the post. -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/ ---(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] dump + restore didn't include schemas
On 30 Jan 2004 at 23:34, Tom Lane wrote: Dan Langille [EMAIL PROTECTED] writes: I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the schemas which existed in the original databases were not created in the new database. New issue? Known bug? New one on me. Look at the log output from when the dump was being restored. I suppose there must be an error message from the CREATE SCHEMA commands --- what is it? I found three of these messages in /var/log/messages on the box which had the problem. ERROR: permission denied for database pg_freebsddiary.org.schemas Nothing else. I don't have the output from the restore. I tried reproducing the problem on another box but schemas were always correctly created. I can't reproduce the problem situation. Sorry. -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] dump + restore didn't include schemas
Dan Langille [EMAIL PROTECTED] writes: I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the schemas which existed in the original databases were not created in the new database. I found three of these messages in /var/log/messages on the box which had the problem. ERROR: permission denied for database pg_freebsddiary.org.schemas Oh, I bet I know what this is: the owners of those schemas don't have CREATE SCHEMA privileges, right? You made the schemas as superuser with CREATE SCHEMA foo AUTHORIZATION bar. 7.4's pg_dump will use AUTHORIZATION so that situations like this restore correctly, but 7.3's pg_dump is stupid and tries to create the schema as its owner. In general I recommend that during an upgrade, you use the new version's pg_dump to dump from the old server. This way you get the benefit of whatever improvements have been made in pg_dump since the previous release. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Transaction callback
I would like to register a transaction callback in the backend. Don't think it's possible today and my question is, how hard would it be to implement something that would enable this registration? Ideally, I'd like a beforeCompletion that is executed prior to the start of the commit process and a afterCompletion that is called when the transaction is commited. The latter would have a status flag indicating if status is prepared (to support 2-phase commits), commited, or rolled back. A related issue is the ability to register a callback that is executed when the connection is first established. Is that possible today? If so, how do I register the function to be called? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] dump + restore didn't include schemas
On 31 Jan 2004 at 11:56, Tom Lane wrote: Dan Langille [EMAIL PROTECTED] writes: I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the schemas which existed in the original databases were not created in the new database. I found three of these messages in /var/log/messages on the box which had the problem. ERROR: permission denied for database pg_freebsddiary.org.schemas Oh, I bet I know what this is: the owners of those schemas don't have CREATE SCHEMA privileges, right? You made the schemas as superuser with CREATE SCHEMA foo AUTHORIZATION bar. 7.4's pg_dump will use AUTHORIZATION so that situations like this restore correctly, but 7.3's pg_dump is stupid and tries to create the schema as its owner. Does it matter that I used pg_dumpall? In general I recommend that during an upgrade, you use the new version's pg_dump to dump from the old server. This way you get the benefit of whatever improvements have been made in pg_dump since the previous release. Should that recommendation be added to the If You Are Upgrading section of INSTALL? -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] dump + restore didn't include schemas
Dan Langille [EMAIL PROTECTED] writes: On 31 Jan 2004 at 11:56, Tom Lane wrote: 7.4's pg_dump will use AUTHORIZATION so that situations like this restore correctly, but 7.3's pg_dump is stupid and tries to create the schema as its owner. Does it matter that I used pg_dumpall? No. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] v7.4.1 text_position() patch
Tatsuo Ishii wrote: It's surprising that nobody noticed the bug until now. It seems it has been there since 7.3 days. I would like to make a back patch for 7.3-stable if nobody objects. It's my bug :( -- sorry about that. Here's a 7.3 patch per Tom's nearby advice. I'll apply if you'd like. Joe Index: src/backend/utils/adt/varlena.c === RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/varlena.c,v retrieving revision 1.92.2.2 diff -c -r1.92.2.2 varlena.c *** src/backend/utils/adt/varlena.c 30 Nov 2003 20:52:37 - 1.92.2.2 --- src/backend/utils/adt/varlena.c 31 Jan 2004 16:50:37 - *** *** 665,673 len1 = (VARSIZE(t1) - VARHDRSZ); len2 = (VARSIZE(t2) - VARHDRSZ); - /* no use in searching str past point where search_str will fit */ - px = (len1 - len2); - if (eml == 1) /* simple case - single byte encoding */ { char *p1, --- 665,670 *** *** 676,681 --- 673,681 p1 = VARDATA(t1); p2 = VARDATA(t2); + /* no use in searching str past point where search_str will fit */ + px = (len1 - len2); + for (p = 0; p = px; p++) { if ((*p2 == *p1) (strncmp(p1, p2, len2) == 0)) *** *** 702,707 --- 702,710 ps2 = p2 = (pg_wchar *) palloc((len2 + 1) * sizeof(pg_wchar)); (void) pg_mb2wchar_with_len((unsigned char *) VARDATA(t2), p2, len2); len2 = pg_wchar_strlen(p2); + + /* no use in searching str past point where search_str will fit */ + px = (len1 - len2); for (p = 0; p = px; p++) { ---(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] Transaction callback
Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: Ideally, I'd like a beforeCompletion that is executed prior to the start of the commit process and a afterCompletion that is called when the transaction is commited. The latter would have a status flag indicating if status is prepared (to support 2-phase commits), commited, or rolled back. And what exactly would this callback do? The transaction commit sequence is sufficiently delicate that I'm not interested in any proposals to call random user-written code in it. The notion of a post-commit callback is even more problematic --- what is it going to do at all? It cannot modify the database, and it cannot do anything that risks getting an error, which seems to leave mighty little scope for useful activity. Why can't we call the callback before we commit so it can modify the database? -- 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] Transaction callback
On 31. jan 2004, at 18:53, Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: Ideally, I'd like a beforeCompletion that is executed prior to the start of the commit process and a afterCompletion that is called when the transaction is commited. The latter would have a status flag indicating if status is prepared (to support 2-phase commits), commited, or rolled back. And what exactly would this callback do? I imagine this would be to enforce that constraints are kept. FOREIGN KEYs can be deferred, and simple CHECK constrains can be simulated with clever foreign keys to dummy tables. Possibly allowing CHECK constraints to be deferred alleviate the need for this? The transaction commit sequence is sufficiently delicate that I'm not interested in any proposals to call random user-written code in it. The notion of a post-commit callback is even more problematic --- what is it going to do at all? It cannot modify the database, and it cannot do anything that risks getting an error, which seems to leave mighty little scope for useful activity. Surely this wouldn't effect the commit sequence. Post-commit actions could be just like cronjobs, but which are run as soon as there is a known need for them (and not otherwise). Ideally triggered triggers could install pre-commit actions during the transaction. The trigger knows: * after this particular insert/update some database logic that cannot be codified into a foreign key constraint is in an inconsistent state and must not be committed unless we are sure that some other action happened later I've had several cases of needing sth like this, but always could hack a solution using several triggers and dummy tables that I could put into an illegal state (with a deferred foreign key). Later another trigger took that table out of the illegal state if the right action was performed. David Helgason Over the Edge Entertainments ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Transaction callback
Bruce Momjian [EMAIL PROTECTED] writes: Why can't we call the callback before we commit so it can modify the database? He wanted that too... A user callback before we start the commit sequence seems doable, although there's some question in my mind of exactly when it should happen. The last point at which it'd really be safe to execute arbitrary actions is just before DeferredTriggerEndXact() --- what if you make a database change that fires deferred triggers? Surely those would have to be executed before we can commit. As you move further down in the sequence, successively larger chunks of functionality become unsafe to invoke. But depending on what you want the callback for, you might not want all that stuff running after you get called; all of it can potentially cause errors and thereby prevent the commit from occurring. I got the impression that Thomas wanted this in order to kluge up some kind of two-phase-commit support, in which case he really needs to get control at the point where we're just about to really truly commit (ie, write the commit WAL record). That's certainly not a location where we want random users to be inserting code; as such I don't think that exposing a callback hook is the right answer. My advice to him is to go in and change the code. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Transaction callback
Tom Lane wrote: I got the impression that Thomas wanted this in order to kluge up some kind of two-phase-commit support, in which case he really needs to get control at the point where we're just about to really truly commit (ie, write the commit WAL record). That's certainly not a location where we want random users to be inserting code; as such I don't think that exposing a callback hook is the right answer. My advice to him is to go in and change the code. Yes, it can't be used for more advanced stuff like 2-phase commit. I know some were asking for callbacks on (clean) session exit, and it seemed like a natural extension. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 7:56 PM To: Bruce Momjian Cc: [EMAIL PROTECTED]; PostgreSQL-development Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION Bruce Momjian [EMAIL PROTECTED] writes: Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? Indeed. SET SESSION AUTH is already allowed only to superusers --- a superuser hardly needs any additional privileges to become whoever he wants. regards, tom lane For exactly the opposite usage: allowing a non-privileged user to take on a different authorization IFF a password is also supplied. This allows a user to use an existing connection (so, for example, connection pooling works) and not require a high priv'd account to then act as a specific (and specifically priv'd) user of the system. E.g., I could then have a user who has only connection privs for the DB and then use a SET SESSION AUTH as a means of logging in as a specific user. What this buys me: Connection pooling (critical for volume web apps) Postgres (DB) level enforcement of privileges via GRANT and REVOKE : so that my priv scheme is consistent across db access methods and I don't have to be too concerned about replicating the authorization logic out in the app layer. == Ezra Epstein. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? Indeed. SET SESSION AUTH is already allowed only to superusers --- a superuser hardly needs any additional privileges to become whoever he wants. It is very helpful for connection pooling/persistent connections. Say I have 10 connections opened as superuser. I can switch the connection authorization per query and let database enforce the rules and access control. For authentication, I can keep a dummy connection. There could be multiple ways to improve this behaviour. 1. If a non super-user attempts set session authorization, let him do so with proper password. 2. Add password to set session authorization as suggested above. I would prefer this actually. In case the application is breached, with option 2, the database is left wide open. With option 1, that may not be the case if initial connection is with a sufficiently unprivilaged user. But then I need to cache the actual password, which is another can of worms..:-( Additionally it would be great if libpq could just authenticate a user without forking a backend. I think some kind of PAM voodoo can be substituted for that but having a libpq frontend is great. I did suggest this earlier as well. Just reiterating.. Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Transaction aborts on syntax error.
Hello, Is is possible to change the transaction behaviour not to abort when a syntax error occurs. I've done some searches on the list, and have not found anything. -ESR- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Transaction aborts on syntax error.
On Fri, Jan 30, 2004 at 07:43:06AM -0800, Edwin S. Ramirez wrote: Is is possible to change the transaction behaviour not to abort when a syntax error occurs. Not currently. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) And as an added bonus, now my computer goes to the toilet for me, leaving me free to spend time on more useful activities! yay slug codefests! (C. Parker) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Update Syntax
Hello, I need Postgres support for the following update syntax : update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2 where id=5) where rownum=3; update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2 where id=table1.parentid) where rownum=3; These type of update statements are the only ones supported by Oracle to update one table from another. Can this be added to the TODO list? Can a cash prize be associated with the TODO item?, I have $1000US allocated for this. -ESR- ---(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] Mixing threaded and non-threaded
OK, thanks. --- Scott Lamb wrote: On Jan 30, 2004, at 4:53 PM, Bruce Momjian wrote: Actually, thinking about this a bit more, that might not even be necessary. Is SIGPIPE-via-(read|write) synchronous or asynchronous? (I.e., is the SIGPIPE guaranteed to arrive during the offending system call?) I was thinking not, but maybe yes. I can't seem to find a straight answer. A lot of documents seem to confuse thread-directed and synchronous, when they're not quite the same thing. SIGALRM-via-alarm() is thread-directed but obviously asynchronous. SIGPIPE is a sychronous signal that is called during the read() in libpq. I am not sure what thread-directed is. Ahh, then the usage in libpq is safe; sorry for the false alarm. The concerns about signal safety are really only for async signals, as the behavior is undefined only when one async signal-unsafe function is called from a signal interrupting another: In the presence of signals, all functions defined by this volume of IEEE?Std?1003.1-2001 shall behave as defined when called from or interrupted by a signal-catching function, with a single exception: when a signal interrupts an unsafe function and the signal-catching function calls an unsafe function, the behavior is undefined. thread-directed, by the way, simply means that the signal is directed at a specific thread, not just some thread in the process that doesn't have it masked. It's the difference between kill() and pthread_kill(). AFAIK, all synchronous signals are thread-directed, but not all thread-directed signals are synchronous. Here the signal is synchronous, so the signal is guaranteed to happen at a safe point (during the read()), so there's no problem. Thanks, Scott Lamb -- 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] Write cache
--On Tuesday, January 27, 2004 23:15:09 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... SO, I consider these disks buggy or not acceptable for use. So do I... I have 6 disks... 250$ each... What kind of warranty? 3 years 24 hours on site (the computer altogather) How Old? 3 months I'd tell them to get you disks that don't lie. and be FIRM about it. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Write cache
On Tue, 27 Jan 2004, Larry Rosenman wrote: ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... SO, I consider these disks buggy or not acceptable for use. So do I... I have 6 disks... 250$ each... What kind of warranty? 3 years 24 hours on site (the computer altogather) How Old? 3 months -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mixing threaded and non-threaded
On Jan 30, 2004, at 4:53 PM, Bruce Momjian wrote: Actually, thinking about this a bit more, that might not even be necessary. Is SIGPIPE-via-(read|write) synchronous or asynchronous? (I.e., is the SIGPIPE guaranteed to arrive during the offending system call?) I was thinking not, but maybe yes. I can't seem to find a straight answer. A lot of documents seem to confuse thread-directed and synchronous, when they're not quite the same thing. SIGALRM-via-alarm() is thread-directed but obviously asynchronous. SIGPIPE is a sychronous signal that is called during the read() in libpq. I am not sure what thread-directed is. Ahh, then the usage in libpq is safe; sorry for the false alarm. The concerns about signal safety are really only for async signals, as the behavior is undefined only when one async signal-unsafe function is called from a signal interrupting another: In the presence of signals, all functions defined by this volume of IEEEĀ StdĀ 1003.1-2001 shall behave as defined when called from or interrupted by a signal-catching function, with a single exception: when a signal interrupts an unsafe function and the signal-catching function calls an unsafe function, the behavior is undefined. thread-directed, by the way, simply means that the signal is directed at a specific thread, not just some thread in the process that doesn't have it masked. It's the difference between kill() and pthread_kill(). AFAIK, all synchronous signals are thread-directed, but not all thread-directed signals are synchronous. Here the signal is synchronous, so the signal is guaranteed to happen at a safe point (during the read()), so there's no problem. Thanks, Scott Lamb ---(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] Write cache
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: This would be the first time a SCSI disk lies about its write caching. There are plenty of low-cost (i.e. IDE) disks out there having a hidden write cache, but AFAIK a generic SCSI tool is usable to enable/disable the write cache. A SCSI disk shouldn't lie about write completion in any case; there's no need to because the interface spec is inherently multi-threaded (unlike IDE). See past discussions. Unless I am reading it wrong, http://www.fcpa.com/download/download/hard-drives/map10krpm-manual.pdf appears to suggest (page 5-21) that the write cache can be disabled. How I am not sure - maybe using one of the tools Andreas mentioned. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 10:46 AM To: [EMAIL PROTECTED] Cc: Bruce Momjian; PostgreSQL-development Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION Ezra Epstein [EMAIL PROTECTED] writes: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? For exactly the opposite usage: allowing a non-privileged user to take on a different authorization IFF a password is also supplied. This allows a user to use an existing connection (so, for example, connection pooling works) and not require a high priv'd account to then act as a specific (and specifically priv'd) user of the system. I do not think SET SESSION AUTH is a suitable replacement for logging in. For one thing, it doesn't apply per-user GUC settings. For OK, what are GUC settings. Can SET SESSION AUTH be extended to do this as needed? another, using it this way in a pooling environment would be completely insecure --- what if you forget to log out, or your attempt to do so is dropped because it was inside a failed transaction block? Well, consider the alternative. A web user logs in to the web app, not to the DB. The web app connects to the DB as a user which has the union of ALL privs of each of the web users! This is the default mode of ALL production web apps. In other words, the alternative is an even bigger security hole Also, in web apps you get to do post-response clean-up. I'd put the RESET SESSION AUTH code there -- all by itself, outside of any transaction. So, on 2 counts I would say the approach I would like to take will result in a more secure application overall. Another objection to doing things this way is that it would just about force people to embed passwords into their SQL scripts, creating another serious source of insecurity. Au contraire! Go do a security audit of most production web system. While the password might not be in SQL it is usually in a config file. E.g., in the server.xml file for a J2EE servlet container as part of the declaration of the jdbc DataSource. And the user is highly priv'd (union of all privs for every user of the application). So what I'd like is a default user that has NO privs. The user logs in, but the credentials are not validated against an internal application-specific (or LDAP/Identity-server provided) authentication but against the database's authentication itself! (Then I'd add password synchronization for an enterprise client to keep their Directory servers and the DB aligned.) In other words: no password or user login is stored at all. It is provided by the user during log in! A much better and much *more* secure approach. == Ezra Epstein ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Write cache
Andrew Dunstan wrote: Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: This would be the first time a SCSI disk lies about its write caching. There are plenty of low-cost (i.e. IDE) disks out there having a hidden write cache, but AFAIK a generic SCSI tool is usable to enable/disable the write cache. A SCSI disk shouldn't lie about write completion in any case; there's no need to because the interface spec is inherently multi-threaded (unlike IDE). See past discussions. Unless I am reading it wrong, http://www.fcpa.com/download/download/hard-drives/map10krpm-manual.pdf appears to suggest (page 5-21) that the write cache can be disabled. How I am not sure - maybe using one of the tools Andreas mentioned. My guess is to look for scsicmd or something like that. The man page usually talks about how to dump the device parametes. Do that and you should see something clear. -- 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 8: explain analyze is your friend
Re: [HACKERS] Transaction aborts on syntax error.
Edwin S. Ramirez wrote: Hello, Is is possible to change the transaction behaviour not to abort when a syntax error occurs. I've done some searches on the list, and have not found anything. No, we need nested transactions for that. We are working on it or at least have a plan. -- 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/docs/faqs/FAQ.html
Re: [HACKERS] Problem with pgtcl on HP
Michael Brusser [EMAIL PROTECTED] writes: We tracked problem to this line in pgtcl.c: putenv (PGCLIENTENCODING=UNICODE); Changing this to Tcl_PutEnv (PGCLIENTENCODING=UNICODE); seems to be a good fix, so far we are not seeing any problems. Looks good to me. Unless anyone else objects, I'll apply this to REL7_4_STABLE and HEAD within 24 hours. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Idea about better configuration options for sort memory
We frequently recommend to people that they increase sort_mem while creating btree indexes. It is reasonable to have a larger setting for that purpose, since (1) a single backend isn't going to be doing multiple index creations in parallel (whereas complex queries could easily be doing multiple sorts or hashes in parallel), and (2) in most installations you won't have a large number of backends doing index creations in parallel. So while sort_mem has to be set on the assumption that you might need quite a few times the nominal setting, this isn't true for index creation. It strikes me that we ought to revise the configuration options to reflect this fact: index creation's memory limit should be driven by a separate parameter instead of using sort_mem. We already have a memory-usage parameter that is larger than sort_mem, and for exactly the same reasons sketched above. It's vacuum_mem. VACUUM is also an operation that you don't expect to be running lots of instances of in parallel, so it's okay for it to eat more than average amounts of RAM. So, what I'd like to do is make btree index creation pay attention to vacuum_mem instead of sort_mem, and rename the vacuum_mem parameter to some more-generic name indicating that it's used for more than just VACUUM. Any objections so far? Now, what should we call it instead? I haven't come up with any compelling thoughts --- the best I can do is big_sort_mem or single_sort_mem. Surely someone out there has a better idea. BTW, does anyone want to lobby for renaming sort_mem at the same time? Since it's used for sizing hash tables as well as sort workspace, it's rather misnamed. I hesitate to rename it because of the potential for confusion though. People are pretty used to the existing name. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Idea about better configuration options for sort memory
On Sat, 31 Jan 2004, Tom Lane wrote: So, what I'd like to do is make btree index creation pay attention to vacuum_mem instead of sort_mem, and rename the vacuum_mem parameter to some more-generic name indicating that it's used for more than just VACUUM. Any objections so far? Why not create a seperate index_mem variable instead? index creation tends to be, I think, less frequent then vacuum, so having a higher value for index_mem then vacuum_mem may make sense ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Idea about better configuration options for sort memory
Marc G. Fournier [EMAIL PROTECTED] writes: On Sat, 31 Jan 2004, Tom Lane wrote: So, what I'd like to do is make btree index creation pay attention to vacuum_mem instead of sort_mem, and rename the vacuum_mem parameter to some more-generic name indicating that it's used for more than just VACUUM. Any objections so far? Why not create a seperate index_mem variable instead? index creation tends to be, I think, less frequent then vacuum, so having a higher value for index_mem then vacuum_mem may make sense ... Well, maybe. What's in the back of my mind is that we may come across other cases besides CREATE INDEX and VACUUM that should use a one-off setting. I think it'd make more sense to have one parameter than keep on inventing new ones. For comparison, SortMem is used for quite a few different purposes, but I can't recall anyone needing to tweak an individual one of those purposes other than CREATE INDEX. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Transaction callback
What I want accomplish has been very well defined already. I'm thinking of javax.transaction.Transaction and javax.transaction.Synchronized, two interfaces readily available in any J2EE application server. The actions that can be performed by implementers of the latter interface (the one that gets the suggested callbacks) are of course limited, especially during post-commit. Nevertheless, they are powerful and can be used in a variety of ways. Some examples. While its true that a post-commit operation cannot modify a database, it might still for instance propagate the outcome of the transaction to another resource outside of the database, it might invalidate a transaction local in-memory cache, or it might clear resources occupied in memory for a rule system, etc. If status in a post-commit is preparing, an exceptional condition could be interpreted as vote rollback. A pre-commit operation should be able to do lot's of interesting read-only operations on the database. Deferred constraints and rule validation comes to mind. It should be able to throw an exception and thus cause a rollback. Some functionality must of course be present that prevents user-written code form doing things that it's not supposed to (like changing things). That's part of my original question really, how hard is it to implement?. Regards, Thomas Hallgren David Helgason [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On 31. jan 2004, at 18:53, Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: Ideally, I'd like a beforeCompletion that is executed prior to the start of the commit process and a afterCompletion that is called when the transaction is commited. The latter would have a status flag indicating if status is prepared (to support 2-phase commits), commited, or rolled back. And what exactly would this callback do? I imagine this would be to enforce that constraints are kept. FOREIGN KEYs can be deferred, and simple CHECK constrains can be simulated with clever foreign keys to dummy tables. Possibly allowing CHECK constraints to be deferred alleviate the need for this? The transaction commit sequence is sufficiently delicate that I'm not interested in any proposals to call random user-written code in it. The notion of a post-commit callback is even more problematic --- what is it going to do at all? It cannot modify the database, and it cannot do anything that risks getting an error, which seems to leave mighty little scope for useful activity. Surely this wouldn't effect the commit sequence. Post-commit actions could be just like cronjobs, but which are run as soon as there is a known need for them (and not otherwise). Ideally triggered triggers could install pre-commit actions during the transaction. The trigger knows: * after this particular insert/update some database logic that cannot be codified into a foreign key constraint is in an inconsistent state and must not be committed unless we are sure that some other action happened later I've had several cases of needing sth like this, but always could hack a solution using several triggers and dummy tables that I could put into an illegal state (with a deferred foreign key). Later another trigger took that table out of the illegal state if the right action was performed. David Helgason Over the Edge Entertainments ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea about better configuration options for sort memory
Tom Lane wrote: Now, what should we call it instead? I haven't come up with any compelling thoughts --- the best I can do is big_sort_mem or single_sort_mem. Surely someone out there has a better idea. BTW, does anyone want to lobby for renaming sort_mem at the same time? Since it's used for sizing hash tables as well as sort workspace, it's rather misnamed. I hesitate to rename it because of the potential for confusion though. People are pretty used to the existing name. Hmmm ... maybe query_work_mem and maintenance_work_mem, or something similar? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Idea about better configuration options for sort memory
On Sat, 31 Jan 2004, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Sat, 31 Jan 2004, Tom Lane wrote: So, what I'd like to do is make btree index creation pay attention to vacuum_mem instead of sort_mem, and rename the vacuum_mem parameter to some more-generic name indicating that it's used for more than just VACUUM. Any objections so far? Why not create a seperate index_mem variable instead? index creation tends to be, I think, less frequent then vacuum, so having a higher value for index_mem then vacuum_mem may make sense ... Well, maybe. What's in the back of my mind is that we may come across other cases besides CREATE INDEX and VACUUM that should use a one-off setting. I think it'd make more sense to have one parameter than keep on inventing new ones. For comparison, SortMem is used for quite a few different purposes, but I can't recall anyone needing to tweak an individual one of those purposes other than CREATE INDEX. Why not a 'default_mem' parameter that auto-sets the others if not explicitly set? note that, at least in my case, I didn't know that sort_mem affected CREATE INDEX, only ORDER/GORUP BYs ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Transaction callback
I don't want to kludge up a 2-phase commit support. Any attempt to provide that must be extremely well thought through :-) But I would like to know more about PostgreSQL capabilities in the area. What kind of XA-support is there? Can I read about this somewhere? (I'm working on an Open Source high-end appserver bundling offer and would of course like to promote PostgreSQL) I'm not sure what Tom means when he says go in and change the code. What code? - thomas Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Tom Lane wrote: I got the impression that Thomas wanted this in order to kluge up some kind of two-phase-commit support, in which case he really needs to get control at the point where we're just about to really truly commit (ie, write the commit WAL record). That's certainly not a location where we want random users to be inserting code; as such I don't think that exposing a callback hook is the right answer. My advice to him is to go in and change the code. Yes, it can't be used for more advanced stuff like 2-phase commit. I know some were asking for callbacks on (clean) session exit, and it seemed like a natural extension. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Tuesday, January 27, 2004 1:35 PM Ezra Epstein [EMAIL PROTECTED] writes: I do not think SET SESSION AUTH is a suitable replacement for logging in. For one thing, it doesn't apply per-user GUC settings. For OK, what are GUC settings. Can SET SESSION AUTH be extended to do this as needed? Not very easily; it's not clear to me how you undo the previous settings taken from the other user, nor how you go back at RESET SESSION AUTH. (It's not so much that you don't know what settings are specified in pg_shadow, as that you don't know what might have been adopted if they'd not been there.) I am also concerned about whether layering such semantics onto SET SESSION AUTH wouldn't break its existing uses. Maybe you could declare by fiat that you don't care and users in this sort of environment don't get to have per-user GUC settings. If they are sharing a webapp front end then maybe they don't need 'em. I dunno how important it really is, but we'd have to think about the implications. Since I still don't know what GUC even stands for, I'll just take the entirely naive approach and assume it doesn't matter for these purposes. another, using it this way in a pooling environment would be completely insecure --- what if you forget to log out, or your attempt to do so is dropped because it was inside a failed transaction block? Well, consider the alternative. A web user logs in to the web app, not to the DB. The web app connects to the DB as a user which has the union of ALL privs of each of the web users! This is the default mode of ALL production web apps. In other words, the alternative is an even bigger security hole No, the alternative is that the web app is responsible for managing security, which I think is the only reasonable place to put the responsibility if you intend to use shared connections. I find it Yes and if you've already had the DBA configure the DB to have group-based security at the rather fine level of granularity that SQL gives, why not have a means of leveraging that -- and thereby simplifying the applications, oh and getting security consistency across all such apps free of charge -- rather than replicate it all in a different tier? simply illusory to think that a shared-connection setup is going to be secure if you don't have complete confidence in the front end. Basically what you're saying is that you're willing to trust the front end to ensure that user A can never do anything over user B's connection, but you're not willing to trust it to enforce security otherwise. That doesn't seem to hold water to me. Another issue with a SET SESSION AUTH extension of this kind is that it would force every multi-user installation to maintain password security whether they want it or not. In an environment where users do not normally use database passwords (perhaps they use IDENT auth instead) it's entirely likely that they'd not bother to select good passwords or guard them. In that case the option to get into someone else's account via SET SESSION AUTH becomes a security hole that people are unlikely to think to plug --- the old out of sight, out of mind problem. This last one is the only concern raised that I can see being one I'd worry over. It makes me think that enabling the alternate mode of SET SESSION AUTH could itself be subject to a DB parameter (settable at startup or via the SET mechanism) and turned off by default. == Ezra Epstein ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4.1 release status - Turkish Locale
We might think that the Turkish-locale problem Devrim Gunduz pointed out is a must-fix, too. But I'm not convinced yet what to do about it. Here is a first try to fix what Devrim Gunduz talked about. Please be patient with me for it is the first major patch I submit and I realize that I blatantly violated many rules of good style in PostgreSQL source code. First, about the problem. Turkish language has two letters i. One is with dot on top and the other is without. Simply as that. The one with dot has the dot both as capital and lower-case and the one without dot has no dot in both upper and lower case... as opposed to English where i has a dot when lower-case and has no dot when upper-case. Problem arise when PostgreSQL, while running with tr_TR locale converts to lower-case an identifier as a table, an index or a column name. If it is written with capital I, tolower() with 'I' as argument will return Turkish specific character: 'i'-without-a-dot what I am afraid will not be shown correctly in your e-mail readers. Let me give some examples. initdb script runs apparently innocent script in file src/backend/utils/mb/conversion_procs/conversion_create.sql to create a couple of functions whose only fault was to declare it their return parameters as VOID. Backend returns error message that type vo d is not found and initdb fails. A nothing suspecting novice user was excited about SERIAL data type he was tail is present in PostgreSQL. It took us with Devrim a lot of time to explain why he need to type SERIAL as SERiAL for now till a workaround is developed. Another case happened with me when I wanted to restore a pg_dump dump. Restore failed because script was creating scripts that belong to PUBLIC. For the solution, after some research we found out that offender is tolower() call in src/backend/parser/scan.l in {identifier} section. tolower() works fine with any locale and with any character save for the Turkish locale and capital 'I' character. So, the obvious solution is to put a check for Turkish locale and 'I' character. Something like this: if( locale is Turkish ident[i] == 'I' ) ident[i] = 'i'; else ident[i] = tolower((unsigned char) ident[i]); Looks rather simple but the hard part was to figure out what is the current locale. To do this I added const char *get_locale_category(const char *category); to src/backend/utils/adt/pg_locale.c that would return locale identifier for the category specified or LC_ALL if category is NULL. I could not find any other function that will return what I need. Please help me to find one because I would hate to introduce a new function. I realize that {identifier} section is very performance critical so I introduced a global variable static int isturkishlocale = -1; at the beginning of src/backend/parser/scan.l It is set to -1 when not yet initialized, 0 if locale is not Turkish and 1 if locale is Turkish. It might not be the way it is usually done in PostgreSQL source code. Could you pleas advise if the name I chose is appropriate and whether there is a more appropriate place to put declaration and initialization. Best regards, Nicolai Tufar Devrim Gunduz trpatch.diff Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] v7.4.1 text_position() patch
Tatsuo Ishii wrote: It's surprising that nobody noticed the bug until now. It seems it has been there since 7.3 days. I would like to make a back patch for 7.3-stable if nobody objects. It's my bug :( -- sorry about that. Here's a 7.3 patch per Tom's nearby advice. I'll apply if you'd like. Joe Thanks. Please apply it. -- Tatsuo Ishii ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Kerberos as source of user name? (Re: [BUGS] segfault in psql on x86_64)
Orion Henry [EMAIL PROTECTED] writes: It appears to be faulting on a kerberos call which is odd because I don't use kerberos for anything. I was a bit surprised to realize that if you compile Kerberos support at all, libpq will try to get a user name from Kerberos in preference to using getpwuid(). This strikes me as odd and surprising behavior. There's certainly no security reason for it, since we are only getting a default user name that can be trivially overridden. Does anyone see a reason why we shouldn't trust getpwuid to supply the default username in all cases? I'm thinking of ripping out fe_setauthsvc/fe_getauthsvc as well ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4.1 release status - Turkish Locale
Nicolai Tufar [EMAIL PROTECTED] writes: We might think that the Turkish-locale problem Devrim Gunduz pointed out is a must-fix, too. But I'm not convinced yet what to do about it. Here is a first try to fix what Devrim Gunduz talked about. I still don't much like having a locale-specific wart in the parser (and the code you give could not work anyway --- for starters, the first argument of setlocale is not a pointer). A possible compromise is to apply ASCII downcasing (same as in keywords.c) for 7-bit-ASCII characters, and apply tolower() only for character codes above 127. In other words unsigned char ch = (unsigned char) ident[i]; if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (ch 127 isupper(ch)) ch = tolower(ch); ident[i] = (char) ch; In reasonably sane locales this will have the same effects as currently, while in unsane locales it will ensure that basic-ASCII identifiers are treated the way we want. Comments? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match