[HACKERS] Dblink and ISDN
We have a request from our customers to link
two database servers through the ISDN link.
We found the dblink in the contrib directory, and
it works ,but there is one big problem.
I'll try to explain it using the sample from
README.dblink:
SAMPLE:
create view myremotetable as select
dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2 from
(select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres
password=postgres'
,'select proname, prosrc from pg_proc') as dblink_p) as t1;
select f1, f2 from myremotetable where f1 like
'bytea%';
When the select is executed:
1. all the data from table
pg_proc are retrieved from remote database
2. then where clause is executed
against that data (on the local side)
This behaviour is OK if the whole story is
happenning on local network, but
in our case data should be send through slow ISDN
connection.
Is it possible to write a rule that uses the
current SQL expression and sends this expression to the remote database ?
In this case only wanted data would be send
through the network.
Thank You in advance
!
Re: [HACKERS] timeout implementation issues, lock timeouts
On Monday 01 April 2002 20:18, Bruce Momjian wrote: > Tom Lane wrote:> > Agreed, only one timeout. > ... We have (at least) two ortogonal reasons why we want to abort a long running transaction: - The long running transaction might compute a result we are not interesed anymore (because it just takes too long to wait for the result). We do NOT always know in advance how patient we will be to wait for the result. Therefore I think the client should tell the server, when his client (user?) got impatinet and aborted the whole transaction... - The long running transaction might hold exclusive locks and therefore decreases (or even nullifies) the overall concurrency. We want to be able to disallow this by design. I think a nice timout criteria would be a maximum lock time for all resources aquired exclusivly within a transaction. This would then affect transaction timeouts as well as statement timeouts with the advantage, the get concurrency guaratees. Robert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
> So the work that would need to be done is asking the driver to request the > timeout via "BEGIN WORK TIMEOUT 5"; getting the backend to parse that > request and set the alarm on each query in that transaction; getting the Well imho that interpretation would be completely unobvious. My first guess would have been, that with this syntax the whole transaction must commit or rollback within 5 seconds. Thus I think we only need statement_timeout. ODBC, same as JDBC wants it at the statement handle level. ODBC also provides for a default that applies to all statement handles of this connection (They call the statement attr QUERY_TIMEOUT, so imho there is room for interpretation whether it applies to selects only, which I would find absurd). Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed patch for ODBC driver w/ C-a-n-c-e-l
Bradley McLean wrote: > > Patch against 7,2 submitted for comment. > > It's a little messy; I had some trouble trying to reconcile the code > style of libpq which I copied from, and odbc. > > Suggestions on what parts look ugly, and or where to send this > (is there a separate ODBC place?) are welcome. Please send it to pgsql-patches or pgsql-odbc. Anyway I would commit your change soon. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] v7.2.1 Released: Critical Bug Fix
> cash I/O improvements (Tom) If it will change the I/O cash flow to more I than O, it will definitely be a success... :-) -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Web: www.suse.dk 2000 FrederiksbergLørdag 11.00-17.00 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] v7.2.1 Released: Critical Bug Fix
Over this past weekend, the PostgreSQL Global Development Group packaged up and put onto our ftp server PostgreSQL v7.2.1 ... a bug fix release, it fixes a critical bug in v7.2: sequence counters will go backwards after a crash Other fixes since v7.2 include: Fix pgaccess kanji-coversion key binding (Tatsuo) Optimizer improvements (Tom) cash I/O improvements (Tom) New Russian FAQ Compile fix for missing AuthBlockSig (Heiko) Additional time zones and time zone fixes (Thomas) Allow psql \connect to handle mixed case database and user names (Tom) Return proper OID on command completion even with ON INSERT rules (Tom) Allow COPY FROM to use 8-bit DELIMITERS (Tatsuo) Fix bug in extract/date_part for milliseconds/microseconds (Tatsuo) Improve handling of multiple UNIONs with different lengths (Tom) contrib/btree_gist improvements (Teodor Sigaev) contrib/tsearch dictionary improvements, see README.tsearch for an additional installation step (Thomas T. Thai, Teodor Sigaev) Fix for array subscripts handling (Tom) Allow EXECUTE of "CREATE TABLE AS ... SELECT" in PL/PgSQL (Tom) Upgrading to v7.2.1 from v7.2 *does not* require a dump/reload, but it is required from all previous releases ... Due to the nature of the bug with the sequence counters, it is *highly* recommended that anyone running v7.2 upgrade to the latest version at their earliest convience ... Marc G. Fournier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] v7.2.1 Released: Critical Bug Fix
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Over this past weekend, the PostgreSQL Global Development Group packaged > up and put onto our ftp server PostgreSQL v7.2.1 ... a bug fix release, it > fixes a critical bug in v7.2: > sequence counters will go backwards after a crash It seems worth pointing out that said bug is not new in 7.2; it has existed in all 7.1.* releases as well. If you were looking for a reason to update to 7.2.* from 7.1.*, this might be a good one. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] v7.2.1 Released: Critical Bug Fix
"Dean Hill" <[EMAIL PROTECTED]> writes: > I was wondering if it is documented as to exactly how to do a minor > upgrade. I've not been able to find it in the past, and I end up doing > a full install, dump/reload. I'm running postgresql on nt/2000 using > cygwin. Thanks -Dean Minor upgrades do not require a dump/restore; the on-disk file format remains the same. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... ---(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] timeout implementation issues
On Mon, 1 Apr 2002, Tom Lane wrote: > On the other hand, we do not have anything in the backend now that > applies to just one statement and then automatically resets afterwards; > and I'm not eager to add a parameter with that behavior just for JDBC's > convenience. It seems like it'd be a big wart. Does that leave us with implementing query timeouts in JDBC (timer in the driver; then the driver sends a cancel request to the backend)? j ---(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] [GENERAL] v7.2.1 Released: Critical Bug Fix
On Tue, 2 Apr 2002, Richard Emberson wrote: > Generally, what is the duration between such an announcement and the > appearence of > the RPMs found on the Download PostgreSQL page? > > Also, the http://www.us.postgresql.org/news.html has no mention of this It takes up to 24 hours for all of the mirror sites to catch up. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] v7.2.1 Released: Critical Bug Fix
Vince Vielhaber <[EMAIL PROTECTED]> writes: > On Tue, 2 Apr 2002, Richard Emberson wrote: >> Generally, what is the duration between such an announcement and the >> appearence of >> the RPMs found on the Download PostgreSQL page? >> >> Also, the http://www.us.postgresql.org/news.html has no mention of this > It takes up to 24 hours for all of the mirror sites to catch up. However, the tarballs were uploaded to the FTP sites several days ago, so you should be able to fetch the code already from any FTP mirror, even if your favorite WWW mirror is still behind. Look under source/v7.2.1/ if you do not see a v7.2.1 link at the top level of your FTP mirror. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] v7.2.1 Released: Critical Bug Fix
On Tue, 2 Apr 2002, Tom Lane wrote: > Vince Vielhaber <[EMAIL PROTECTED]> writes: > > On Tue, 2 Apr 2002, Richard Emberson wrote: > >> Generally, what is the duration between such an announcement and the > >> appearence of > >> the RPMs found on the Download PostgreSQL page? > >> > >> Also, the http://www.us.postgresql.org/news.html has no mention of this > > > It takes up to 24 hours for all of the mirror sites to catch up. > > However, the tarballs were uploaded to the FTP sites several days ago, > so you should be able to fetch the code already from any FTP mirror, > even if your favorite WWW mirror is still behind. > Look under source/v7.2.1/ if you do not see a v7.2.1 link at the top > level of your FTP mirror. The links didn't exist until just a little while ago so most of the mirrors won't have them yet. source/v7.2.1 does exist tho. RPMs aren't available yet. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dblink and ISDN
Darko Prenosil wrote:
> SAMPLE:
>
> create view myremotetable as
> select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
> user=postgres password=postgres'
> ,'select proname, prosrc from pg_proc') as dblink_p)
> as t1;
>
>
>
> select f1, f2 from myremotetable where f1 like 'bytea%';
>
You could write the query directly instead of using a view, i.e.
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from pg_proc')
as dblink_p WHERE proname LIKE 'bytea%') as t1;
>
>
> Is it possible to write a rule that uses the current SQL expression and
> sends this expression to the remote database ?
>
> In this case only wanted data would be send through the network.
>
I'm not experienced in using PostgreSQL rules, but I don't see a way to
access the current SQL expression. Hopefully someone more knowledgeable
will chime in here.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Dblink and ISDN
Joe Conway wrote:
> Darko Prenosil wrote:
>
>> SAMPLE:
>>
>> create view myremotetable as
>> select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
>> from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
>> user=postgres password=postgres'
>> ,'select proname, prosrc from pg_proc') as
>> dblink_p) as t1;
>>
>>
>>
>> select f1, f2 from myremotetable where f1 like 'bytea%';
>>
>
> You could write the query directly instead of using a view, i.e.
>
> select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
> from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
> user=postgres password=postgres','select proname, prosrc from pg_proc')
> as dblink_p WHERE proname LIKE 'bytea%') as t1;
>
Oops, messed up my cut and paste, and forgot to double the quotes around
bytea%. This one I tested ;) to work fine:
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from pg_proc
WHERE proname LIKE ''bytea%''')
as dblink_p) as t1;
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] escape sequence conflicting w/ backup (i.e. pg_dump)
I have a postgresql DB that contains a lot of HTML code. As you know, HTML contains numerous backslash( \ ) characters. When I use pg_dump to backup the DB I get a "CopyReadAttribute: end of record marker corrupted" error. Is there any way to circumvent this problem so that I can backup a DB with HTML code stored in it? Thanks, Matias >CopyReadAttribute: end of record marker corrupted > >This message comes out if the COPY data contains \. not immediately >followed by newline (\n). I'm guessing that you have some backslashes >that need to be doubled --- backslash is an escape character for COPY. _ Chat with friends online, try MSN Messenger: http://messenger.msn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Patch to add real cancel to ODBC driver
Patch against 7,2 submitted for comment.
It's a little messy; I had some trouble trying to reconcile the code
style of libpq which I copied from, and odbc.
Suggestions on what parts look ugly, and or where to send this
(is there a separate ODBC place?) are welcome.
This seems to work just fine; Now, when our users submit a 2 hour
query with four million row sorts by accident, then cancel it 30 seconds
later, it doesn't bog down the server ...
regards,
-Brad
diff -cr postgresql-7.2/src/interfaces/odbc/connection.c
postgresql-7.2-brad/src/interfaces/odbc/connection.c
*** postgresql-7.2/src/interfaces/odbc/connection.c Sun Dec 30 18:09:42 2001
--- postgresql-7.2-brad/src/interfaces/odbc/connection.cWed Mar 27 10:04:45
2002
***
*** 19,24
--- 19,27
#include
#include
#include
+ #ifndef WIN32
+ #include
+ #endif
#include "environ.h"
#include "socket.h"
***
*** 828,835
}
break;
case 'K': /* Secret key (6.4 protocol) */
! (void) SOCK_get_int(sock, 4); /* pid
*/
! (void) SOCK_get_int(sock, 4); /* key
*/
break;
case 'Z': /* Backend is ready for new
query (6.4) */
--- 831,839
}
break;
case 'K': /* Secret key (6.4 protocol) */
! self->be_pid = SOCK_get_int(sock, 4);
/* pid */
! self->be_key = SOCK_get_int(sock, 4);
/* key */
! qlog("conn=%u, Backend
pid=%u\n",self,self->be_pid);
break;
case 'Z': /* Backend is ready for new
query (6.4) */
***
*** 1837,1839
--- 1841,1903
value = BLCKSZ;
return value;
}
+
+ int
+ CC_send_cancel_request(const ConnectionClass *conn)
+ {
+ #ifdef WIN32
+ int save_errno = (WSAGetLastError());
+ #else
+ int save_errno = errno;
+ #endif
+ int tmpsock = -1;
+ struct
+ {
+ uint32 packetlen;
+ CancelRequestPacket cp;
+ } crp;
+
+ /* Check we have an open connection */
+ if (!conn)
+ return FALSE;
+
+ if (conn->sock == NULL )
+ {
+ return FALSE;
+ }
+
+ /*
+ * We need to open a temporary connection to the postmaster. Use the
+ * information saved by connectDB to do this with only kernel calls.
+ */
+ if ((tmpsock = socket(AF_INET, SOCK_STREAM, 0)) < 0)
+ {
+ return FALSE;
+ }
+ if (connect(tmpsock, (struct sockaddr *)&(conn->sock->sadr),
+ sizeof(conn->sock->sadr)) < 0)
+ {
+ return FALSE;
+ }
+
+ /*
+ * We needn't set nonblocking I/O or NODELAY options here.
+ */
+ crp.packetlen = htonl((uint32) sizeof(crp));
+ crp.cp.cancelRequestCode = (MsgType) htonl(CANCEL_REQUEST_CODE);
+ crp.cp.backendPID = htonl(conn->be_pid);
+ crp.cp.cancelAuthCode = htonl(conn->be_key);
+
+ if (send(tmpsock, (char *) &crp, sizeof(crp), 0) != (int) sizeof(crp))
+ {
+ return FALSE;
+ }
+
+ /* Sent it, done */
+ closesocket(tmpsock);
+ #ifdef WIN32
+ WSASetLastError(save_errno);
+ #else
+ errno = save_errno;
+ #endif
+ }
diff -cr postgresql-7.2/src/interfaces/odbc/connection.h
postgresql-7.2-brad/src/interfaces/odbc/connection.h
*** postgresql-7.2/src/interfaces/odbc/connection.h Mon Nov 5 12:46:38 2001
--- postgresql-7.2-brad/src/interfaces/odbc/connection.hTue Mar 26 14:45:35
2002
***
*** 125,130
--- 125,146
chartty[PATH_SIZE];
} StartupPacket6_2;
+ /* Transferred from pqcomm.h: */
+
+
+ typedef ProtocolVersion MsgType;
+
+ #define PG_PROTOCOL(m,n) (((m) << 16) | (n))
+ #define CANCEL_REQUEST_CODE PG_PROTOCOL(1234,5678)
+
+ typedef struct CancelRequestPacket
+ {
+ /* Note that each field is stored in network byte order! */
+ MsgType cancelRequestCode; /* code to identify a cancel
+request */
+ unsigned intbackendPID; /* PID of client's backend */
+ unsigned intcancelAuthCode; /* secret key to authorize cancel */
+ } CancelRequestPacket;
+
/*Structure to hold all the connection attr
[HACKERS] Threading in libpg on Solaris
I'm working on getting libpq to function in a multi-threaded program on Solaris and I was getting errors back from the library about being unable to receive from the server. It turns out that on Solaris you need to compile libpq with the -D_REENTRANT flags set so that it defines errno to be a function call instead of a global variable. Once I did this the program worked without any problems. You want to consider making this flag standard (or at least provide a configure option to compile a thread-ready version of libpq) as it may save someone else the hassle of trying to figure out what went wrong. Martin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] status of IPv6 Support for INET/CIDR types
Hi, We are implementing a database for maintaining our IP addresses. Looking in the current documentation, it seems that INET/CIDR types only support IPv4 addresses until now, although http://archives.postgresql.org/pgsql-patches/2001-09/msg00236.php seems to suggest a patch for IPv6 has been ready for some time now. What is the status of IPv6 types at this moment? -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dblink and ISDN
Out of curiousity, what happens if the remove server is unavailable?
- Original Message -
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "Darko Prenosil" <[EMAIL PROTECTED]>
Cc: "Hackers" <[EMAIL PROTECTED]>
Sent: Tuesday, April 02, 2002 12:58 PM
Subject: Re: [HACKERS] Dblink and ISDN
> Joe Conway wrote:
> > Darko Prenosil wrote:
> >
> >> SAMPLE:
> >>
> >> create view myremotetable as
> >> select dblink_tok(t1.dblink_p,0) as f1,
dblink_tok(t1.dblink_p,1) as f2
> >> from (select dblink('hostaddr=127.0.0.1 port=5432
dbname=template1
> >> user=postgres password=postgres'
> >> ,'select proname, prosrc from pg_proc') as
> >> dblink_p) as t1;
> >>
> >>
> >>
> >> select f1, f2 from myremotetable where f1 like 'bytea%';
> >>
> >
> > You could write the query directly instead of using a view, i.e.
> >
> > select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1)
as f2
> > from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
> > user=postgres password=postgres','select proname, prosrc from
pg_proc')
> > as dblink_p WHERE proname LIKE 'bytea%') as t1;
> >
>
> Oops, messed up my cut and paste, and forgot to double the quotes
around
> bytea%. This one I tested ;) to work fine:
> select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as
f2
> from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
> user=postgres password=postgres','select proname, prosrc from
pg_proc
> WHERE proname LIKE ''bytea%''')
> as dblink_p) as t1;
>
> Joe
>
>
> ---(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
[HACKERS]
CREATE TABLE mytesting ( dosnotmatter text ); CREATE INDEX myunique ON mytesting oid; will this help to make sure the oid is unique? and is that right? if in fact the oid roll over, and insertation fail. Will reinsert get an new oid or the same oid retry. Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Dblink and ISDN
Rod Taylor wrote:
> Out of curiousity, what happens if the remove server is unavailable?
>
I tried it against a bogus IP, and this is what I got:
test=# select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1)
as f2 from (select dblink('hostaddr=123.45.67.8
dbname=template1','select proname, prosrc from pg_proc WHERE proname
LIKE ''bytea%''') as dblink_p) as t1;
ERROR: dblink: connection error: could not connect to server:
Connection timed out
Is the server running on host 123.45.67.8 and accepting
TCP/IP connections on port 5432?
test=#
dblink just uses libpq to make a client connection, and thus inherits
libpq's response.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] maxint reached?
Has anyone seen this: ERROR: dtoi4: integer out of range on 7.1.3 What worries me, is that at startup time, the log shows: DEBUG: database system was shut down at 2002-04-02 23:16:52 EEST DEBUG: CheckPoint record at (82, 1928435208) DEBUG: Redo record at (82, 1928435208); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 517528628; NextOid: 2148849196 DEBUG: database system is in production state Note the NextOid, while i /usr/include/machine/limits.h defines INT_MAX as 2147483647. Are oid really singed ints? Daniel PS: This database indeed has an increasing oid counter in that range. Grep from the log shows DEBUG: NextTransactionId: 386003914; NextOid: 1551075952 DEBUG: NextTransactionId: 397667914; NextOid: 1643984428 DEBUG: NextTransactionId: 53748; NextOid: 1864857132 DEBUG: NextTransactionId: 450233305; NextOid: 1888540204 DEBUG: NextTransactionId: 454987662; NextOid: 1917687340 DEBUG: NextTransactionId: 501775621; NextOid: 2078209580 DEBUG: NextTransactionId: 517524499; NextOid: 2148849196 DEBUG: NextTransactionId: 517528628; NextOid: 2148849196 this is from one month ago. ---(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]
Alex Lau <[EMAIL PROTECTED]> writes: > CREATE TABLE mytesting ( dosnotmatter text ); > CREATE INDEX myunique ON mytesting oid; > will this help to make sure the oid is unique? No, but CREATE UNIQUE INDEX myunique ON mytesting (oid); would do the trick. 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] maxint reached?
An followup to my previous post. It turned out to be an query containing "oid = somenumber" called from perl script. Is it possible that the default type conversion functions do not work as expected? Changing this to "oid = oid(somenumber)" worked as expected. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] maxint reached?
Daniel Kalchev <[EMAIL PROTECTED]> writes: > It turned out to be an query containing "oid = somenumber" called from perl script. >Is it possible that the default type conversion functions do not work as expected? No, but you do have to cast an oversize value to oid explicitly to prevent it from being taken as int4, eg regression=# select oid = 24 from int4_tbl; ERROR: dtoi4: integer out of range regression=# select oid = 24::oid from int4_tbl; << works >> (In releases before about 7.1 you'd have had to single-quote the literal, too.) This is one of a whole raft of cases involving undesirable assignment of types to numeric constants; see past complaints about int4 being used where int2 or int8 was wanted, numeric vs float8 constants, etc etc. We're still looking for a promotion rule that does what you want every time... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] maxint reached?
>>>Tom Lane said: > This is one of a whole raft of cases involving undesirable assignment > of types to numeric constants; see past complaints about int4 being used > where int2 or int8 was wanted, numeric vs float8 constants, etc etc. > We're still looking for a promotion rule that does what you want every > time... So in essence this means that my best bet is to again dump/reload the database... Even pgaccess has hit this problem as it uses oid=something in the queries. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] maxint reached?
Daniel Kalchev <[EMAIL PROTECTED]> writes: > So in essence this means that my best bet is to again dump/reload the > database... Either that or fix your queries to cast the literals explicitly. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Suggestions please: names for function cachability attributes
Since I'm about to have to edit pg_proc.h to add a namespace column, I thought this would be a good time to revise the current proiscachable column into the three-way cachability distinction we've discussed before. But I need some names for the values, and I'm not satisfied with the ideas I've had so far. To refresh people's memory: what we want is to be able to distinguish between functions that are: 1. Strictly cachable (a/k/a constant-foldable): given fixed input values, the same result value will always be produced, for ever and ever, amen. Examples: addition operator, sin(x). Given a call of such a function with all-constant input values, the system is entitled to fold the function call to a constant on sight. 2. Cachable within a single command: given fixed input values, the result will not change if the function were to be repeatedly evaluated within a single SQL command; but the result could change over time. Examples: now(); datetime-related operations that depend on the current timezone (or other SET-able variables); any function that looks in database tables to determine its result. 3. Totally non-cachable: result may change from one call to the next, even within a single SQL command. Examples: nextval(), random(), timeofday(). (Yes, timeofday() and now() are in different categories. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) Currently the system can only distinguish cases 1 and 3, so functions that are really case 2 have to be labeled as case 3; this prevents a lot of useful optimizations. In particular, it is safe to use expressions involving only case-1 and case-2 functions as indexscan conditions, whereas case-3 functions cannot be optimized into an indexscan. So this is an important fix to make. BTW, because of MVCC semantics, case 2 covers more ground than you might think. We are interested in functions whose values cannot change during a single "scan", ie, while the intra-transaction command counter does not increment. So functions that do SELECTs are actually guaranteed to be case 2, even if stuff outside the function is changing the table being looked at. My problem is picking names for the three categories of functions. Currently we use "with (isCachable)" to identify category 1, but it seems like this name might actually be more sensible for category 2. I'm having a hard time picking simple names that convey these meanings accurately, or even with a reasonable amount of suggestiveness. Comments, ideas? 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] Suggestions please: names for function cachability attributes
My 2 cents. Level 1. with (isCachableStatic) Level 2. with (isCachableDynamic) Level 3. default In my mind (isCachable) sounds like level 1 On Tuesday 02 April 2002 03:40 pm, Tom Lane wrote: > Since I'm about to have to edit pg_proc.h to add a namespace column, > I thought this would be a good time to revise the current proiscachable > column into the three-way cachability distinction we've discussed > before. But I need some names for the values, and I'm not satisfied > with the ideas I've had so far. > > To refresh people's memory: what we want is to be able to distinguish > between functions that are: > > 1. Strictly cachable (a/k/a constant-foldable): given fixed input > values, the same result value will always be produced, for ever and > ever, amen. Examples: addition operator, sin(x). Given a call > of such a function with all-constant input values, the system is > entitled to fold the function call to a constant on sight. > > 2. Cachable within a single command: given fixed input values, the > result will not change if the function were to be repeatedly evaluated > within a single SQL command; but the result could change over time. > Examples: now(); datetime-related operations that depend on the current > timezone (or other SET-able variables); any function that looks in > database tables to determine its result. > > 3. Totally non-cachable: result may change from one call to the next, > even within a single SQL command. Examples: nextval(), random(), > timeofday(). (Yes, timeofday() and now() are in different categories. > See > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datet >ime.html#FUNCTIONS-DATETIME-CURRENT) > > Currently the system can only distinguish cases 1 and 3, so functions > that are really case 2 have to be labeled as case 3; this prevents a lot > of useful optimizations. In particular, it is safe to use expressions > involving only case-1 and case-2 functions as indexscan conditions, > whereas case-3 functions cannot be optimized into an indexscan. So this > is an important fix to make. > > BTW, because of MVCC semantics, case 2 covers more ground than you might > think. We are interested in functions whose values cannot change during > a single "scan", ie, while the intra-transaction command counter does > not increment. So functions that do SELECTs are actually guaranteed to > be case 2, even if stuff outside the function is changing the table > being looked at. > > My problem is picking names for the three categories of functions. > Currently we use "with (isCachable)" to identify category 1, but it > seems like this name might actually be more sensible for category 2. > I'm having a hard time picking simple names that convey these meanings > accurately, or even with a reasonable amount of suggestiveness. > > Comments, ideas? > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(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] Suggestions please: names for function cachability
Tom Lane wrote: > BTW, because of MVCC semantics, case 2 covers more ground than you might > think. We are interested in functions whose values cannot change during > a single "scan", ie, while the intra-transaction command counter does > not increment. So functions that do SELECTs are actually guaranteed to > be case 2, even if stuff outside the function is changing the table > being looked at. > > My problem is picking names for the three categories of functions. > Currently we use "with (isCachable)" to identify category 1, but it > seems like this name might actually be more sensible for category 2. > I'm having a hard time picking simple names that convey these meanings > accurately, or even with a reasonable amount of suggestiveness. > > Comments, ideas? > How about: case 1: Cachable case 2: ScanCachable or Optimizable case 3: NonCachable Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Unicode ready?
Is PostgreSQL unicode compliant/ready? Does it store/export text in Unicode wide-character format, or single character strings? ---(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] Suggestions please: names for function cachability attributes
* Tom Lane ([EMAIL PROTECTED]) [020402 16:42]: > Since I'm about to have to edit pg_proc.h to add a namespace column, > I thought this would be a good time to revise the current proiscachable > column into the three-way cachability distinction we've discussed > before. But I need some names for the values, and I'm not satisfied > with the ideas I've had so far. Invariant Cachable Noncachable ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] mailing list problem
OK. Here is a test posting to hackers and Ccing to Marc Oleg On Tue, 26 Mar 2002, Marc G. Fournier wrote: > > ah, then that's gotta be it ... can you do me a favor and email > pgsql-hackers a simple test, so that I can get the email out of hte > approved messages and show you what I'm seeing? > > On Tue, 26 Mar 2002, Oleg Bartunov wrote: > > > On Tue, 26 Mar 2002, Marc G. Fournier wrote: > > > > > > > > Okay, this is most strange ... you are getting confirmation that you are > > > subscribed, right? Can I see a copy of that message? For some reason, > > > > No, I didn't get any confirmation :-( > > > > > when I go through the moderated postings, everything for you comes in as > > > 'unknown@anonymous is posting for ...', so I'm wondering if maybe its > > > trying to subscribe you as this 'unknown@anonymous', which, of course, > > > won't get backt o you ... > > > > > > On Mon, 25 Mar 2002, Oleg Bartunov wrote: > > > > > > > Marc, > > > > > > > > I've resubscribed (yesterday) to pg mailing lists but didn't have > > > > any response. Perhaps, there are problem with mailing list software? > > > > > > > > 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 > > > > > > > > > > > > > > > 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 > > > > > 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] SET NOT NULL/DROP NOT NULL patch
This is a complete patch to implement changing the nullability of an
attribute. It passes all regressions tests. It includes its own quite
comprehensive regression test suite and documentation. It prevents you from
modifying system tables, non-table relations, system attributes, primary
keys and columns containing NULLs. It fully supports inheritance. I have
made some small changes to TODO to reflect this new functionality, plus
corrected some other TODO items.
The only thing I haven't checked are my ecpg changes. I would like someone
with more ecpg experience to check my preproc.y changes.
Please consider for 7.3!
Since I have now added two new large functions to command.c, I propose that
sometime before 7.3 beta, command.c is refactored and an alter.c created.
There is lots of common code in the Alter* functions that should be reused.
Chris
? GNUmakefile
? alpha-patch.txt
? config.cache
? config.log
? config.status
? configure.out
? domaintest.sql
? null.txt
? regression.txt
? contrib/tree
? contrib/tree.tar.gz
? contrib/intagg/int_aggregate.sql
? src/GNUmakefile
? src/Makefile.global
? src/backend/postgres
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/psql/psql
? src/bin/scripts/createlang
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/libecpg.so.3
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgeasy/libpgeasy.so.2
? src/interfaces/libpq/libpq.so.2
? src/pl/plpgsql/src/libplpgsql.so.1
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/postgres.core
? src/test/regress/results
? src/test/regress/tmp_check
? src/test/regress/expected/bak.out
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
Index: doc/TODO
===
RCS file: /projects/cvsroot/pgsql/doc/TODO,v
retrieving revision 1.775
diff -c -r1.775 TODO
*** doc/TODO2002/03/25 20:56:08 1.775
--- doc/TODO2002/03/27 06:12:03
***
*** 185,192
o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
o Add ALTER FUNCTION
o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! o ALTER TABLE ADD PRIMARY KEY (Christopher Kings-Lynne)
! o ALTER TABLE ADD UNIQUE (Christopher Kings-Lynne)
o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
rows with DEFAULT value
--- 185,193
o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
o Add ALTER FUNCTION
o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! o -ALTER TABLE ADD PRIMARY KEY (Tom)
! o -ALTER TABLE ADD UNIQUE (Tom)
! o -ALTER TABLE ALTER COLUMN SET/DROP NOT NULL (Christopher Kings-Lynne)
o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
rows with DEFAULT value
Index: doc/src/sgml/ref/alter_table.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.40
diff -c -r1.40 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 2002/03/06 20:42:38 1.40
--- doc/src/sgml/ref/alter_table.sgml 2002/03/27 06:12:03
***
*** 29,34
--- 29,36
ALTER [ COLUMN ] column { SET
DEFAULT value | DROP DEFAULT }
ALTER TABLE [ ONLY ] table [ * ]
+ ALTER [ COLUMN ] column { SET |
+DROP } NOT NULL
+ ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column SET
STATISTICS integer
ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column SET STORAGE
{PLAIN | EXTERNAL | EXTENDED | MAIN}
***
*** 168,173
--- 170,178
allow you to set or remove the default for the column. Note that defaults
only apply to subsequent INSERT commands; they do not
cause rows already in the table to change.
+The ALTER COLUMN SET/DROP NOT NULL forms allow you to
+change whether a column is marked to allow NULL values or to reject NULL
+values.
The ALTER COLUMN SET STATISTICS form allows you to
set the statistics-gathering target for subsequent
operations.
***
*** 276,281
--- 281,297
To rename an existing table:
ALTER TABLE
[HACKERS] SET NOT NULL / DROP NOT NULL as an HREF!
OK, http://members.iinet.net.au/~klfamily/alternotnull.txt.gz This is an identical patch to what I've submitted twice now and hasn't come through... This is a complete patch to implement changing the nullability of an attribute. It passes all regressions tests. It includes its own quite comprehensive regression test suite and documentation. It prevents you from modifying system tables, non-table relations, system attributes, primary keys and columns containing NULLs. It fully supports inheritance. I have made some small changes to TODO to reflect this new functionality, plus corrected some other TODO items. The only thing I haven't checked are my ecpg changes. I would like someone with more ecpg experience to check my preproc.y changes. Please consider for 7.3! Since I have now added two new large functions to command.c, I propose that sometime before 7.3 beta, command.c is refactored and an alter.c created. There is lots of common code in the Alter* functions that should be reused. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] hackers mail broken?
Original titles:
Patch to add real cancel to ODBC driver
Patch to add real can--cel to ODBC driver
Bruce, sorry to bother you, would you forward this onto the list?
I can't post for reasons I can't fathom.
-
Patch against 7,2 submitted for comment.
It's a little messy; I had some trouble trying to reconcile the code
style of libpq which I copied from, and odbc.
Suggestions on what parts look ugly, and or where to send this
(is there a separate ODBC place?) are welcome.
This seems to work just fine; Now, when our users submit a 2 hour
query with four million row sorts by accident, then cancel it 30 seconds
later, it doesn't bog down the server ...
regards,
-Brad
diff -cr postgresql-7.2/src/interfaces/odbc/connection.c
postgresql-7.2-brad/src/interfaces/odbc/connection.c
*** postgresql-7.2/src/interfaces/odbc/connection.c Sun Dec 30 18:09:42 2001
--- postgresql-7.2-brad/src/interfaces/odbc/connection.cWed Mar 27 10:04:45
2002
***
*** 19,24
--- 19,27
#include
#include
#include
+ #ifndef WIN32
+ #include
+ #endif
#include "environ.h"
#include "socket.h"
***
*** 828,835
}
break;
case 'K': /* Secret key (6.4 protocol) */
! (void) SOCK_get_int(sock, 4); /* pid
*/
! (void) SOCK_get_int(sock, 4); /* key
*/
break;
case 'Z': /* Backend is ready for new
query (6.4) */
--- 831,839
}
break;
case 'K': /* Secret key (6.4 protocol) */
! self->be_pid = SOCK_get_int(sock, 4);
/* pid */
! self->be_key = SOCK_get_int(sock, 4);
/* key */
! qlog("conn=%u, Backend
pid=%u\n",self,self->be_pid);
break;
case 'Z': /* Backend is ready for new
query (6.4) */
***
*** 1837,1839
--- 1841,1903
value = BLCKSZ;
return value;
}
+
+ int
+ CC_send_cancel_request(const ConnectionClass *conn)
+ {
+ #ifdef WIN32
+ int save_errno = (WSAGetLastError());
+ #else
+ int save_errno = errno;
+ #endif
+ int tmpsock = -1;
+ struct
+ {
+ uint32 packetlen;
+ CancelRequestPacket cp;
+ } crp;
+
+ /* Check we have an open connection */
+ if (!conn)
+ return FALSE;
+
+ if (conn->sock == NULL )
+ {
+ return FALSE;
+ }
+
+ /*
+ * We need to open a temporary connection to the postmaster. Use the
+ * information saved by connectDB to do this with only kernel calls.
+ */
+ if ((tmpsock = socket(AF_INET, SOCK_STREAM, 0)) < 0)
+ {
+ return FALSE;
+ }
+ if (connect(tmpsock, (struct sockaddr *)&(conn->sock->sadr),
+ sizeof(conn->sock->sadr)) < 0)
+ {
+ return FALSE;
+ }
+
+ /*
+ * We needn't set nonblocking I/O or NODELAY options here.
+ */
+ crp.packetlen = htonl((uint32) sizeof(crp));
+ crp.cp.cancelRequestCode = (MsgType) htonl(CANCEL_REQUEST_CODE);
+ crp.cp.backendPID = htonl(conn->be_pid);
+ crp.cp.cancelAuthCode = htonl(conn->be_key);
+
+ if (send(tmpsock, (char *) &crp, sizeof(crp), 0) != (int) sizeof(crp))
+ {
+ return FALSE;
+ }
+
+ /* Sent it, done */
+ closesocket(tmpsock);
+ #ifdef WIN32
+ WSASetLastError(save_errno);
+ #else
+ errno = save_errno;
+ #endif
+ }
diff -cr postgresql-7.2/src/interfaces/odbc/connection.h
postgresql-7.2-brad/src/interfaces/odbc/connection.h
*** postgresql-7.2/src/interfaces/odbc/connection.h Mon Nov 5 12:46:38 2001
--- postgresql-7.2-brad/src/interfaces/odbc/connection.hTue Mar 26 14:45:35
2002
***
*** 125,130
--- 125,146
chartty[PATH_SIZE];
} StartupPacket6_2;
+ /* Transferred from pqcomm.h: */
+
+
+ typedef ProtocolVersion MsgType;
+
+ #define PG_PROTOCOL(m,n) (((m) << 16) | (n))
+ #define CANCEL_REQUEST_CODE PG_PROTOCOL(1234,5678)
+
+ typedef struct CancelRequestPacket
+ {
+ /* Note that each field is stored in network byte order! */
+ MsgType cancelRequestCode; /* code to identify a cancel
+request */
+ unsigned
Re: [HACKERS] [GENERAL] v7.2.1 Released: Critical Bug Fix
Generally, what is the duration between such an announcement and the appearence of the RPMs found on the Download PostgreSQL page? Also, the http://www.us.postgresql.org/news.html has no mention of this upgrade. Thanks. Richard "Marc G. Fournier" wrote: > Over this past weekend, the PostgreSQL Global Development Group packaged > up and put onto our ftp server PostgreSQL v7.2.1 ... a bug fix release, it > fixes a critical bug in v7.2: > > sequence counters will go backwards after a crash > > Other fixes since v7.2 include: > > Fix pgaccess kanji-coversion key binding (Tatsuo) > Optimizer improvements (Tom) > cash I/O improvements (Tom) > New Russian FAQ > Compile fix for missing AuthBlockSig (Heiko) > Additional time zones and time zone fixes (Thomas) > Allow psql \connect to handle mixed case database and user names (Tom) > Return proper OID on command completion even with ON INSERT rules (Tom) > Allow COPY FROM to use 8-bit DELIMITERS (Tatsuo) > Fix bug in extract/date_part for milliseconds/microseconds (Tatsuo) > Improve handling of multiple UNIONs with different lengths (Tom) > contrib/btree_gist improvements (Teodor Sigaev) > contrib/tsearch dictionary improvements, see README.tsearch for >an additional installation step (Thomas T. Thai, Teodor Sigaev) > Fix for array subscripts handling (Tom) > Allow EXECUTE of "CREATE TABLE AS ... SELECT" in PL/PgSQL (Tom) > > Upgrading to v7.2.1 from v7.2 *does not* require a dump/reload, but it is > required from all previous releases ... > > Due to the nature of the bug with the sequence counters, it is *highly* > recommended that anyone running v7.2 upgrade to the latest version at > their earliest convience ... > > Marc G. Fournier > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Contrib update
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. --- Gilles DAROLD wrote: > Hi Justin, > > I have a new updated version of the Ora2Pg tool which correct many > problems and add some new features, could you or someone else update > the contrib directory. > (download at: http://www.samse.fr/GPL/ora2pg/ora2pg-1.8.tar.gz) > > I also just post a new tool in replacement of the Oracle XSQL Servlet, > use to create dynamic web application with XML/XSLT. > > Let me know if it can take place under the contrib directory. > (http://www.samse.fr/GPL/pxsql/) > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- 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 ---(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] [GENERAL] v7.2.1 Released: Critical Bug Fix
I was wondering if it is documented as to exactly how to do a minor upgrade. I've not been able to find it in the past, and I end up doing a full install, dump/reload. I'm running postgresql on nt/2000 using cygwin. Thanks -Dean -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marc G. Fournier Sent: Tuesday, April 02, 2002 9:08 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [GENERAL] v7.2.1 Released: Critical Bug Fix Over this past weekend, the PostgreSQL Global Development Group packaged up and put onto our ftp server PostgreSQL v7.2.1 ... a bug fix release, it fixes a critical bug in v7.2: sequence counters will go backwards after a crash Other fixes since v7.2 include: Fix pgaccess kanji-coversion key binding (Tatsuo) Optimizer improvements (Tom) cash I/O improvements (Tom) New Russian FAQ Compile fix for missing AuthBlockSig (Heiko) Additional time zones and time zone fixes (Thomas) Allow psql \connect to handle mixed case database and user names (Tom) Return proper OID on command completion even with ON INSERT rules (Tom) Allow COPY FROM to use 8-bit DELIMITERS (Tatsuo) Fix bug in extract/date_part for milliseconds/microseconds (Tatsuo) Improve handling of multiple UNIONs with different lengths (Tom) contrib/btree_gist improvements (Teodor Sigaev) contrib/tsearch dictionary improvements, see README.tsearch for an additional installation step (Thomas T. Thai, Teodor Sigaev) Fix for array subscripts handling (Tom) Allow EXECUTE of "CREATE TABLE AS ... SELECT" in PL/PgSQL (Tom) Upgrading to v7.2.1 from v7.2 *does not* require a dump/reload, but it is required from all previous releases ... Due to the nature of the bug with the sequence counters, it is *highly* recommended that anyone running v7.2 upgrade to the latest version at their earliest convience ... Marc G. Fournier ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] How to give permission to others on data directory
Create a separate user and both of you use sudo to start the database. If you're insistent on keeping yourself owner of the data then use sudo to give permission to your project partner to start the database. On Sunday 31 March 2002 05:49 am, Amit Khare wrote: > Hi Peter, > Thank you very much for your reply . > However the problem is that we don't want to create separate user for > server. If "initdb" takes my login name and makes me owner of the data > directory then how should I be able to give permission to other users in > this case my project partner? > > Thanks again > > Regards > Amit Khare > - Original Message - > From: Peter Eisentraut <[EMAIL PROTECTED]> > To: Amit Khare <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Sunday, March 31, 2002 4:20 AM > Subject: Re: [HACKERS] How to give permission to others on data directory > > > Amit Khare writes: > > > (1) Actually we are doing project on PostgreSQL in group of two. We > > installed individual copy of PostgreSQL into our group directory. > > > > (2) When I created data directory and ran "initdb" it makes me( takes > > > my > > login name ) as the owner of data directory. > > > > (3) The problem is that now my partner cannot start the postmaster > > > since > > he does not have right on the data directory. Further one cannot set right > on the data directory more than 700 . > > > > (4) For time being we hacked the postmaster.c and commented the line > > starting from 318 which actually test the permission on data directory. > Then my partner was able to run the postmaster since now I gave him > rights(770) on the data directory(But changed rights on postgresql.conf > file to 744). > > > > (5) Is there a clean way by which my partner can start postmaster on > > data directory created by me. > > > Create a separate user for the server and give yourself and your partner > > access to it. > > > > -- > > Peter Eisentraut [EMAIL PROTECTED] > > > > > > ---(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 > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > > On the other hand, we do not have anything in the backend now that > > applies to just one statement and then automatically resets afterwards; > > and I'm not eager to add a parameter with that behavior just for JDBC's > > convenience. It seems like it'd be a big wart. > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? No, I think we have to find a way to do this in the backend; just not sure how yet. I see the problem Tom is pointing out, that SET is ignored if the transaction has already aborted: test=> begin; BEGIN test=> lkjasdf; ERROR: parser: parse error at or near "lkjasdf" test=> set server_min_messages = 'log'; WARNING: current transaction is aborted, queries ignored until end of transaction block *ABORT STATE* test=> so if the transaction aborted, the reset of the statement_timeout would not happen. The only way the application could code this would be with this: BEGIN WORK; query; SET statement_timeout = 4; query; SET statement_timeout = 0; query; COMMIT; SET statement_timeout = 0; Basically, it does the reset twice, once assuming the transaction doesn't abort, and another assuming it does abort. Is this something that the JDBC and ODBC drivers can do automatically? -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_temp.XX.0
I found out, that there are some probably temporary relations in one of my databases, with names (that show in vacuum verbose output) like pg_temp.12720.0. Are these the result of CREATE TEMP TABLE or simmilar and if so, can such relations be safely dropped? Perhaps a good idea to add some vacuum functionality to do this. Daniel ---(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] maxint reached?
>>>Tom Lane said: > Daniel Kalchev <[EMAIL PROTECTED]> writes: > > So in essence this means that my best bet is to again dump/reload the > > database... > > Either that or fix your queries to cast the literals explicitly. There is more to it: customer=# select max(oid) from croute; max - -2144025472 (1 row) How to handle this? Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] maxint reached?
>>>Tom Lane said: > Daniel Kalchev <[EMAIL PROTECTED]> writes: > > So in essence this means that my best bet is to again dump/reload the > > database... > > Either that or fix your queries to cast the literals explicitly. Sorry for the incomplete reply: this does not work: customer=# select max(oid) from croute; max - -2144025472 (1 row) this does work: customer=# select oid(max(oid)) from croute; oid 2150941824 (1 row) weird, isn't it? I guess max should return the same type as it's arguments, no? Daniel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Jessica Perry Hekman wrote: > On Tue, 2 Apr 2002, Bruce Momjian wrote: > > > BEGIN WORK; > > query; > > SET statement_timeout = 4; > > query; > > SET statement_timeout = 0; > > query; > > COMMIT; > > SET statement_timeout = 0; > > > > Basically, it does the reset twice, once assuming the transaction > > doesn't abort, and another assuming it does abort. Is this something > > that the JDBC and ODBC drivers can do automatically? > > I can't speak for ODBC. Seems like in JDBC, Connection::commit() would > call code clearing the timeout, and Statement::executeQuery() and > executeUpdate() would do the same. Well, then a SET variable would work fine for statement-level queries. Just add the part for commit/abort transaction. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
On Tue, 2 Apr 2002, Bruce Momjian wrote: > BEGIN WORK; > query; > SET statement_timeout = 4; > query; > SET statement_timeout = 0; > query; > COMMIT; > SET statement_timeout = 0; > > Basically, it does the reset twice, once assuming the transaction > doesn't abort, and another assuming it does abort. Is this something > that the JDBC and ODBC drivers can do automatically? I can't speak for ODBC. Seems like in JDBC, Connection::commit() would call code clearing the timeout, and Statement::executeQuery() and executeUpdate() would do the same. j ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] status of IPv6 Support for INET/CIDR types
Reinoud van Leeuwen wrote: > Hi, > > We are implementing a database for maintaining our IP addresses. Looking > in the current documentation, it seems that INET/CIDR types only support > IPv4 addresses until now, although > http://archives.postgresql.org/pgsql-patches/2001-09/msg00236.php > seems to suggest a patch for IPv6 has been ready for some time now. > > What is the status of IPv6 types at this moment? Some merging of code from the BIND code and our IPv4 changes need to be made. No one has done it yet. -- 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Since both the JDBC and ODBC specs have essentially the same symantics for this, I would hope this is done in the backend instead of both interfaces. --Barry Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > >>On the other hand, we do not have anything in the backend now that >>applies to just one statement and then automatically resets afterwards; >>and I'm not eager to add a parameter with that behavior just for JDBC's >>convenience. It seems like it'd be a big wart. > > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? > > j > > > ---(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 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] Suggestions please: names for function cachability
Tom Lane writes: > Since I'm about to have to edit pg_proc.h to add a namespace column, > I thought this would be a good time to revise the current proiscachable > column into the three-way cachability distinction we've discussed > before. But I need some names for the values, and I'm not satisfied > with the ideas I've had so far. Well, for one thing, we might want to change the name to the correct spelling "cacheable". > 1. Strictly cachable (a/k/a constant-foldable): given fixed input > values, the same result value will always be produced, for ever and > ever, amen. Examples: addition operator, sin(x). Given a call > of such a function with all-constant input values, the system is > entitled to fold the function call to a constant on sight. deterministic (That's how SQL99 calls it.) > 2. Cachable within a single command: given fixed input values, the > result will not change if the function were to be repeatedly evaluated > within a single SQL command; but the result could change over time. > Examples: now(); datetime-related operations that depend on the current > timezone (or other SET-able variables); any function that looks in > database tables to determine its result. "cacheable" seems OK for this. > 3. Totally non-cachable: result may change from one call to the next, > even within a single SQL command. Examples: nextval(), random(), > timeofday(). (Yes, timeofday() and now() are in different categories. > See >http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) not deterministic, not cacheable -- Peter Eisentraut [EMAIL PROTECTED] ---(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] ANALYZE after restore
Hi, Would it be an idea to have pg_dump append an ANALYZE; command to the end of its dumps to assist newbies / inexperienced admins? Reason being is that I noticed that when I just restored a 50MB dump that the pg_statistic table had no contents... I think it'd be an idea... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Suggestions please: names for function cachability
On Tue, 2 Apr 2002, Peter Eisentraut wrote: > Tom Lane writes: > > > Since I'm about to have to edit pg_proc.h to add a namespace column, > > I thought this would be a good time to revise the current proiscachable > > column into the three-way cachability distinction we've discussed > > before. But I need some names for the values, and I'm not satisfied > > with the ideas I've had so far. > > Well, for one thing, we might want to change the name to the correct > spelling "cacheable". > > > 1. Strictly cachable (a/k/a constant-foldable): given fixed input > > values, the same result value will always be produced, for ever and > > ever, amen. Examples: addition operator, sin(x). Given a call > > of such a function with all-constant input values, the system is > > entitled to fold the function call to a constant on sight. > > deterministic > > (That's how SQL99 calls it.) > > > 2. Cachable within a single command: given fixed input values, the > > result will not change if the function were to be repeatedly evaluated > > within a single SQL command; but the result could change over time. > > Examples: now(); datetime-related operations that depend on the current > > timezone (or other SET-able variables); any function that looks in > > database tables to determine its result. > > "cacheable" seems OK for this. SQL99 suggests that there are only two types of user defined routines: deterministic and 'possibly non-deterministic'. However, in section 11.49 it defines ::= DETERMINISTIC | NOT DETERMINISTIC So the real problem is how to qualify this. TRANSACTIONAL DETERMINISTIC or NOT DETERMINISTIC CACHEABLE are the only ways that come to mind. I'll admit that I don't like either. > > > 3. Totally non-cachable: result may change from one call to the next, > > even within a single SQL command. Examples: nextval(), random(), > > timeofday(). (Yes, timeofday() and now() are in different categories. > > See >http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) > > not deterministic, not cacheable > > Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] SHOW ALL as a query result
Hi All, Now that Tom's modified the EXPLAIN output to appear as a query result, maybe SHOW and SHOW ALL should also be modified in that way. The current NOTICE: business is a bit messy, and it sure would assist projects just as pgAccess, phpPgAdmin and pgAdmin with displaying configuration! Also, what else could be usefully modified? Chris ps. >>BTW, see: ~/pgsql/src/backend/commands/explain.c >>for the new functions Tom Lane wrote which send explain results to the >>front end as if they were from a select statement. Very informative. >>Specifically see: >> begin_text_output(CommandDest dest, char *title); >> do_text_output(TextOutputState *tstate, char *aline); >> do_text_output_multiline(TextOutputState *tstate, char *text); >> end_text_output(TextOutputState *tstate); > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ANALYZE after restore
On Wed, 3 Apr 2002 09:40:13 +0800 "Christopher Kings-Lynne" <[EMAIL PROTECTED]> wrote: > Hi, > > Would it be an idea to have pg_dump append an ANALYZE; command to the end of > its dumps to assist newbies / inexperienced admins? That strikes me as a good idea; a lot of the questions we get on -general and on IRC are solved by suggesting "have you run ANALYZE?" And that is only the sub-section of the user community that takes the time to track down the problem and posts about it to the mailing list -- I shudder to think how many people have never taken the time to tune their database at all. Given that ANALYZE is now a separate command, so there is no need to run a VACUUM (which could be much more expensive); furthermore, since ANALYZE now only takes a statistical sampling of the full table, it shouldn't take very long, even on large tables. However, I'd say we should make this behavior optional, controlled by a command-line switch, but it should be enabled by default. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(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] ANALYZE after restore
On Wed, 3 Apr 2002, Christopher Kings-Lynne wrote: > Hi, > > Would it be an idea to have pg_dump append an ANALYZE; command to the end of > its dumps to assist newbies / inexperienced admins? I do not think this is desired behaviour. Firstly, pg_dump is not just for restoring data to the system. Presumably another flag would need to be added to pg_dump to prevent an ANALYZE being appended. This is messing and, in my opinion, it goes against the 'does what it says it does' nature of Postgres. Secondly, in experienced admins are not going to get experienced with database management unless they see that their database runs like a dog and they have to read the manual. Gavin ---(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] SHOW ALL as a query result
Christopher Kings-Lynne writes: > Now that Tom's modified the EXPLAIN output to appear as a query result, > maybe SHOW and SHOW ALL should also be modified in that way. The current > NOTICE: business is a bit messy, and it sure would assist projects just as > pgAccess, phpPgAdmin and pgAdmin with displaying configuration! Yes, I was going to suggest this myself. It would be very useful to have this information available to the JDBC driver so you could query, say, the default transaction isolation. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SHOW ALL as a query result
Christopher Kings-Lynne wrote: > Hi All, > > Now that Tom's modified the EXPLAIN output to appear as a query > result, maybe SHOW and SHOW ALL should also be modified in that way. > The current NOTICE: business is a bit messy, and it sure would > assist projects just as pgAccess, phpPgAdmin and pgAdmin with > displaying configuration! > > Also, what else could be usefully modified? > > Chris > > ps. > > >>> BTW, see: ~/pgsql/src/backend/commands/explain.c for the new >>> functions Tom Lane wrote which send explain results to the front >>> end as if they were from a select statement. Very informative. >>> Specifically see: begin_text_output(CommandDest dest, char >>> *title); do_text_output(TextOutputState *tstate, char *aline); >>> do_text_output_multiline(TextOutputState *tstate, char *text); >>> end_text_output(TextOutputState *tstate); I was also thinking about this, but the EXPLAIN approach is only useful if you never want to select on the output. Another approach might be to write a function, say show_all(), and then modify gram.y to make: SHOW ALL; - equivalent to - SELECT show_all(); so that you could do: SELECT show_var() FROM (SELECT show_all()) as s WHERE show_var_name() LIKE 'wal%'; or something like that. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE after restore
Gavin Sherry <[EMAIL PROTECTED]> writes: > On Wed, 3 Apr 2002, Christopher Kings-Lynne wrote: >> Would it be an idea to have pg_dump append an ANALYZE; command to the end of >> its dumps to assist newbies / inexperienced admins? > I do not think this is desired behaviour. I agree with Gavin here ... a forced VACUUM or ANALYZE after a restore will just get in the way of people who know what they're doing, and it's not at all clear that it will help people who do not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
Hi All, As part of my ongoing quest to understand grammar files, I've been trying to implement BETWEEN SYMMETRIC/ASYMMETRIC. I've attached my current work. Can someone please look and tell me if I'm on the right track? With this patch, I get parse errors after BETWEEN if I go: SELECT 2 BETWEEN ASYMMETRIC 1 and 3; or SELECT 2 BETWEEN SYMMETRIC 1 and 3; So it doesn't seem to be working - I don't know why!! Don't look at the NOT BETWEEN stuff - I've not done it yet. I was forced to put SYMMETRIC and ASYMMETRIC as reserved words - anything else seemed to give shift/reduce errors. Is there anything I can do about that? 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] BETWEEN SYMMETRIC/ASYMMETRIC
*sigh*
I actually attached the diff this time...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher
> Kings-Lynne
> Sent: Wednesday, 3 April 2002 12:26 PM
> To: Hackers
> Subject: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
>
>
> Hi All,
>
> As part of my ongoing quest to understand grammar files, I've
> been trying to
> implement BETWEEN SYMMETRIC/ASYMMETRIC.
>
> I've attached my current work. Can someone please look and tell me if I'm
> on the right track? With this patch, I get parse errors after
> BETWEEN if I
> go:
>
> SELECT 2 BETWEEN ASYMMETRIC 1 and 3;
>
> or
>
> SELECT 2 BETWEEN SYMMETRIC 1 and 3;
>
> So it doesn't seem to be working - I don't know why!!
>
> Don't look at the NOT BETWEEN stuff - I've not done it yet.
>
> I was forced to put SYMMETRIC and ASYMMETRIC as reserved words - anything
> else seemed to give shift/reduce errors. Is there anything I can do about
> that?
>
> Chris
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
Index: src/backend/parser/gram.y
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.299
diff -c -r2.299 gram.y
*** src/backend/parser/gram.y 2002/04/01 04:35:38 2.299
--- src/backend/parser/gram.y 2002/04/03 04:21:13
***
*** 148,154
simple_select
%type alter_column_default
! %type drop_behavior, opt_drop_behavior
%type createdb_opt_list, createdb_opt_item
%typeopt_equal
--- 148,154
simple_select
%type alter_column_default
! %type drop_behavior, opt_drop_behavior, opt_asymmetry
%type createdb_opt_list, createdb_opt_item
%typeopt_equal
***
*** 344,350
IMMEDIATE, INITIALLY, INOUT,
OFF, OUT,
PATH_P, PENDANT,
! REPLACE, RESTRICT,
TRIGGER,
WITHOUT
--- 344,350
IMMEDIATE, INITIALLY, INOUT,
OFF, OUT,
PATH_P, PENDANT,
! REPLACE, RESTRICT, SYMMETRIC, ASYMMETRIC,
TRIGGER,
WITHOUT
***
*** 4948,4964
b->booltesttype = IS_NOT_UNKNOWN;
$$ = (Node *)b;
}
! | a_expr BETWEEN b_expr AND b_expr %prec BETWEEN
{
! $$ = makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $3),
! makeA_Expr(OP, "<=", $1, $5));
}
! | a_expr NOT BETWEEN b_expr AND b_expr %prec BETWEEN
{
$$ = makeA_Expr(OR, NULL,
! makeA_Expr(OP, "<", $1, $4),
! makeA_Expr(OP, ">", $1, $6));
}
| a_expr IN in_expr
{
--- 4948,4975
b->booltesttype = IS_NOT_UNKNOWN;
$$ = (Node *)b;
}
! | a_expr BETWEEN opt_asymmetry b_expr AND b_expr
%prec BETWEEN
{
! if ($3 == SYMMETRIC)
! $$ = makeA_Expr(OR, NULL,
! makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=",
$1, $4),
! makeA_Expr(OP, "<=",
$1, $6)),
! makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=",
$1, $6),
! makeA_Expr(OP, "<=",
$1, $4))
! );
! else
! $$ = makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=",
$1, $4),
! makeA_Expr(OP, "<=",
$1, $6));
!
}
! | a_expr NOT BETWEEN opt_asymmetry b_expr AND b_expr%prec
BETWEEN
{
Re: [HACKERS] Suggestions please: names for function cachability
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Well, for one thing, we might want to change the name to the correct > spelling "cacheable". Is that correct? I looked in the Oxford English Dictionary, the Random House Dictionary, and a couple other dictionaries of less substantial heft, and could not find anything authoritative at all. RH gives the derived forms "cached" and "caching"; OED offers nothing. I'd be interested to see an authoritative reference for the spelling of the adjective form. Possibly we should avoid the issue by using another word ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestions please: names for function cachability
On Tue, 02 Apr 2002 23:39:35 -0500 "Tom Lane" <[EMAIL PROTECTED]> wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Well, for one thing, we might want to change the name to the correct > > spelling "cacheable". > > Is that correct? Apparently, other people are confused as well: http://www.xent.com/FoRK-archive/august97/0431.html FWIW, google has ~30,000 results for -eable, and ~8,000 results for -able. A couple other software projects (notably Apache Jakarta) use -eable. My preference would be for -eable, but that's just on the basis of "it looks right", which is hardly authoritative. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(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] BETWEEN SYMMETRIC/ASYMMETRIC
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > I was forced to put SYMMETRIC and ASYMMETRIC as reserved words - anything > else seemed to give shift/reduce errors. Is there anything I can do about > that? First thought is "don't try to be cute": forget the opt_asymmetry clause, and instead spell out six productions a_expr BETWEEN b_expr AND b_expr a_expr NOT BETWEEN b_expr AND b_expr a_expr BETWEEN SYMMETRIC b_expr AND b_expr a_expr NOT BETWEEN SYMMETRIC b_expr AND b_expr a_expr BETWEEN ASYMMETRIC b_expr AND b_expr a_expr NOT BETWEEN ASYMMETRIC b_expr AND b_expr I have not checked that this will work, but usually the cure for parse conflicts is to postpone the decision about which production applies. The reason opt_asymmetry forces SYMMETRIC and ASYMMETRIC to become reserved is that it requires a premature decision. Given, say a_expr BETWEEN . SYMMETRIC (where . means "where we are now" and SYMMETRIC is the current lookahead token), an LR(1) parser *must* decide whether to reduce opt_asymmetry as null, or to shift (implying that opt_asymmetry will be SYMMETRIC); it has to make this choice before it can look beyond the SYMMETRIC token. If SYMMETRIC might be a regular identifier then this is unresolvable without more lookahead. The six-production approach avoids this problem by not requiring any shift/reduce decisions to be made until an entire clause is available. On second thought there may be no other way out. Consider foo BETWEEN SYMMETRIC - bar AND baz Is SYMMETRIC a keyword (with "-" a prefix operator) or an identifier (with "-" infix)? This example makes me think that SYMMETRIC has to become reserved. But I wanted to point out that opt_asymmetry is certainly a loser based on lookahead distance. 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])
[HACKERS] Odd psql \i behaviour
What's with this? current pwd = /home/chriskl usa=# \i ddlpack/kl_setnotnull.sql <-- tab completes properly DROP CREATE usa=# \i ~/ddlpack/kl_setnotnull.sql <-- tab completes properly ~/ddlpack/kl_setnotnull.sql: No such file or directory usa=# Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Locale support is now on by default
The determination of locale is now done as follows: collate/ctype: initdb --lc-collate, initdb --locale, LC_ALL, LC_COLLATE, LANG messages/monetary/numeric/time: Have GUC variables lc_messages, etc. The default is "", which means to inherit from the environment (or whatever setlocale() does with it). However, initdb will initialize postgresql.conf containing assignments to these variables determined as with collate/ctype above. So the "real" defaults are consistent with collate/ctype. initdb --no-locale is the same as initdb --locale=C, for convenience. Let's see if these rules end up making sense to everybody. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] Locale support is now on by default
Peter Eisentraut <[EMAIL PROTECTED]> writes: > The determination of locale is now done as follows: > initdb --lc-collate, initdb --locale, LC_ALL, LC_COLLATE, LANG > initdb --no-locale is the same as initdb --locale=C, for convenience. I'm confused; what is the default behavior if you don't give any switches to initdb? BTW, something that's been bothering me for awhile is that the notice we stuck into the backend a couple versions back (about "this locale disables LIKE optimizations") is being hidden by initdb, because you decided recently that it was okay to route all the backend's commentary to /dev/null so as to hide xlog.c's startup chattiness. I don't object to getting rid of that chattiness, but 2>/dev/null is throwing the baby out with the bathwater (consider outright failure messages, for instance). It might be that Bruce's recent changes to elog levels allow a graceful compromise about backend messages during initdb. I haven't looked, but maybe initdb could run the backend with message level one notch higher than LOG to suppress all the normal-case messages without masking not- so-normal cases. 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_temp.XX.0
You can stop the postmaster and start the postgres binary with the -O flag and delete the pg_temp tables. We don't have a cleanup for these failed backends but we should. Normally they are cleaned up. --- Daniel Kalchev wrote: > I found out, that there are some probably temporary relations in one of my > databases, with names (that show in vacuum verbose output) like > pg_temp.12720.0. > > Are these the result of CREATE TEMP TABLE or simmilar and if so, can such > relations be safely dropped? Perhaps a good idea to add some vacuum > functionality to do this. > > Daniel > > > ---(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 > -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SHOW ALL as a query result
> -Original Message- > From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] > Sent: 03 April 2002 02:50 > To: Hackers > Cc: Tom Lane; [EMAIL PROTECTED] > Subject: SHOW ALL as a query result > > > Hi All, > > Now that Tom's modified the EXPLAIN output to appear as a > query result, maybe SHOW and SHOW ALL should also be modified > in that way. The current > NOTICE: business is a bit messy, and it sure would assist > projects just as pgAccess, phpPgAdmin and pgAdmin with > displaying configuration! It certainly would. Of course we've worked around it now though :-(, but future enhancements Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Re : Solaris Performance - Profiling (Solved)
Hi Tom,
How about we include this and have configure somehow ensure the Solaris
users get it automatically?
There are a *bunch* of Solaris users out there.
:-)
Regards and best wishes,
Justin Clift
Mark kirkwood wrote:
>
> On Wed, 2002-04-03 at 04:02, Tom Lane wrote:
> >
> > Hmm. Where exactly did you get those numbers from? I see 4118.54 sec
> > as the total CPU accounted for in the profile.
> >
> odd ...the call graph has 4047.53 and the flat graph has 4118.54
> >
> > Hmm. Assuming that the profile data is trustworthy and the queries are
> > indeed the same (did you compare EXPLAIN output?), it seems that
> > Solaris' problem is a spectacularly bad qsort() implementation.
> >
> A bit surfing finds heaps of unhappy Solaris qsort users... apparently
> it cannot sort lists with many repeated items... so our GROUP BY will be
> causing it grief here
> >
> > It might be entertaining to snarf a qsort off the net (from glibc,
> > perhaps) and link it into Postgres to see if you get better results.
> >
> > regards, tom lane
> >
> Indeed it is - obtained qsort.c from Freebsd CVS and rebuilt Postgresql :
> The query now takes 6 seconds instead of 1 hour ! Thanks for an
> excellent suggestion.
>
> For those in need to a quick fix :
>
> I did a cheap and dirty mod to src/backend/utils/sort/Makefile
>
> changed OBJS = logtape.o -> OBJS = qsort.o logtape.o
>
> and copied qsort.c into this directory
>
> (had to comment out a couple of lines to compile under Solaris :
>
> /*#include
> __FBSDID("$FreeBSD: src/lib/libc/stdlib/qsort.c,v 1.11 2002/03/22
> 21:53:10 obrien Exp $");
> */
>
> )
>
> What do you think about providing something like this for the Solaris
> port ? (since its clearly quicker...)
>
> regards
>
> Mark
>
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
