[GENERAL] Debian packages
Hi All Where do I find the Debian apt-get source path for PostgreSQL 8.1 Regards, Riaan van der Westhuizen
Re: [GENERAL] Debian packages
am 09.11.2005, um 9:00:08 +0200 mailte Riaan van der Westhuizen folgendes: > Hi All > > > > Where do I find the Debian apt-get source path for PostgreSQL 8.1 deb http://people.debian.org/~mpitt/packages/sarge-backports/ / 8.1 on my sarge is running ;-) [EMAIL PROTECTED]:~$ echo "select version();" | psql -U webmaster scholl version --- PostgreSQL 8.1.0 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to use indexes for partial match at beginning
> Well, for starters, see if PostgreSQL is currently using any indexes via > EXPLAIN. First rule of performance tuning: don't. I'm designing a new application. Data is not available yet. I'm using Postgres 8.1 in Windows. Database encoding is UTF-8 lc_ctype is Estonian_Estonia.1257. lc_collate is Estonian currently. However I can set lc_collate to C if this solves this issue. Doc says that " to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, several custom operator classes exist" I don't understand "non-C locale". Does this mean lc_collate or also some other lc_ setting ? > If it's not (which is probably the case), then your best bet is to > create functional indexes; ie: > > CREATE INDEX mytable__col1_4 ON mytable( substring( col1 for 4 ) ); > > You can then either > > SELECT ... WHERE substring( col1 for 4 ) = blah I need to optimize queries with variable number of characters in beginning like SELECT ... WHERE substring( col1 for 1 ) = 'f' SELECT ... WHERE substring( col1 for 2 ) = 'fo' SELECT ... WHERE substring( col1 for 3 ) = 'foo' etc This approach requires creating 10 indexes for each column which is unreasonable. In my current dbms, Microsoft Visual FoxPro I have a single index CREATE INDEX i1 ON mytable(col1) I can use queries: WHERE col1 BETWEEN 'f' and 'f'+CHR(255) WHERE col1 BETWEEN 'fo' and 'fo'+CHR(255) WHERE col1 BETWEEN 'foo' and 'foo'+CHR(255) All those queries can use same index automatically in all locales. CHR(255) is last character in any lc_collate sequence. CHR(255) is not used in col1 data. How to get same functionality in Postgres ? Does there exist unicode special character which is greater than all other chars ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best way to use indexes for partial match at beginning
On Wed, Nov 09, 2005 at 12:37:25PM +0200, Andrus wrote: > I'm using Postgres 8.1 in Windows. Database encoding is UTF-8 > lc_ctype is Estonian_Estonia.1257. > lc_collate is Estonian currently. However I can set lc_collate to C if this > solves this issue. > > Doc says that > " to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, > several custom operator classes exist" > > I don't understand "non-C locale". Does this mean lc_collate or also some > other lc_ setting ? lc == locale. There are several different locale settings but collation affects ordering. And Estonian is not C (obviously). > I need to optimize queries with variable number of characters in beginning > like > > SELECT ... WHERE substring( col1 for 1 ) = 'f' > SELECT ... WHERE substring( col1 for 2 ) = 'fo' > SELECT ... WHERE substring( col1 for 3 ) = 'foo' > etc If you use queries like: SELECT ... WHERE col1 LIKE 'fo%' it can use an index declared like: CREATE INDEX myindex on mytable(col1 text_pattern_ops); > In my current dbms, Microsoft Visual FoxPro I have a single index > > CREATE INDEX i1 ON mytable(col1) > > I can use queries: > > WHERE col1 BETWEEN 'f' and 'f'+CHR(255) > WHERE col1 BETWEEN 'fo' and 'fo'+CHR(255) > WHERE col1 BETWEEN 'foo' and 'foo'+CHR(255) Well, you could do that in postgresql too, you just need to use the SQL standard concatination operator. WHERE col1 BETWEEN 'f' and 'f' || chr(255); > How to get same functionality in Postgres ? > Does there exist unicode special character which is greater than all other > chars ? Umm, I don't think so. Order is defined by the locale, not the character set. My guess is that text_pattern_ops is the way to go. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpe128cxB9UO.pgp Description: PGP signature
[GENERAL] insert on duplicate update?
hi, is there in postgres anything similar to: insert on duplicate update? here is the problem i need a solution for: I need to insert in pg_type a row which was previously selected from there and with slight modifications, the problem is that when i try to insert the row there might be a row with the same 'typname' which is sort of primary key. The second problem is that when doing those operations i don't want to depend on the specific structure of the pg_type catalog. I want my procedure to work even if in some future release additional columns to pg_type are added. So what i want is to insert a row in a table that was previously selected from the same table but if there is primary key collision i want to update the row that collided. And all these without being dependant on the table structure. -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] insert on duplicate update?
Peter Filipov wrote: hi, is there in postgres anything similar to: insert on duplicate update? here is the problem i need a solution for: I need to insert in pg_type a row which was previously selected from there and with slight modifications, the problem is that when i try to insert the row there might be a row with the same 'typname' which is sort of primary key. The second problem is that when doing those operations i don't want to depend on the specific structure of the pg_type catalog. Are you certain it will always be safe to do this? How will you provide meaningful values for columns you don't know exist? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] insert on duplicate update?
On Wed, Nov 09, 2005 at 01:31:59PM +0200, Peter Filipov wrote: > hi, > is there in postgres anything similar to: > insert on duplicate update? > here is the problem i need a solution for: > I need to insert in pg_type a row which was previously selected from there > and What's wrong with CREATE TYPE? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpQzeWEFW26k.pgp Description: PGP signature
Re: [GENERAL] psql error on quitting...
Tom Lane writes: > Jerry LeVan <[EMAIL PROTECTED]> writes: >> I just upgraded from 8.0.4 to 8.1.0 this afternoon and the only thing >> "bad" I have noticed is that whenever I quit psql I get a message: >> could not save history to file "/Users/jerry/.psql_history": Invalid >> argument >> This is on MacOS X 10.4.3 > > The Postgres code in that area hasn't changed at all. input.c has: --8<---cut here---start->8--- revision 1.45 date: 2005/06/10 15:40:41; author: momjian; state: Exp; lines: +2 -2 Use saveHistory so we get a nice error message on failure. --8<---cut here---end--->8--- So, the error probably also occured in 8.0.4, but just wasn't reported at all. regards, Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] resetting superuser password
I have lost the superuser (user postgres) password, but I still have the unix root password. Is there anything I can do to reset the postgres user's password (short of reinstalling Postgres)? Obviously, I can su to unix user postgres, but this does not seem to advance my cause, as doing "psql -U postgres" still prompts me for a password, which is not the same as the unix postgres user's password. OS: RedHat 9 Help! Thanks, Marc ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] psql error on quitting...
Tom was right, on the Mac libreadline is linked to libedit. My "solution" was to comment out the line reporting the error in the program "input.c" Jerry On Nov 9, 2005, at 7:27 AM, Andreas Seltenreich wrote: Tom Lane writes: Jerry LeVan <[EMAIL PROTECTED]> writes: I just upgraded from 8.0.4 to 8.1.0 this afternoon and the only thing "bad" I have noticed is that whenever I quit psql I get a message: could not save history to file "/Users/jerry/.psql_history": Invalid argument This is on MacOS X 10.4.3 The Postgres code in that area hasn't changed at all. input.c has: --8<---cut here---start->8--- revision 1.45 date: 2005/06/10 15:40:41; author: momjian; state: Exp; lines: +2 -2 Use saveHistory so we get a nice error message on failure. --8<---cut here---end--->8--- So, the error probably also occured in 8.0.4, but just wasn't reported at all. regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] resetting superuser password
On Wed, Nov 09, 2005 at 04:42:39AM -0800, mad wrote: > I have lost the superuser (user postgres) password, but I still have > the unix root password. Is there anything I can do to reset the > postgres user's password (short of reinstalling Postgres)? Edit pg_hba.conf to let you in without a password, then you can login and change it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgptg5ywZNHLU.pgp Description: PGP signature
[GENERAL] temporary tables
The PostgreSQL docs say that "Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction." If I create a temporary table through a java.sql.Statement and then call close() on that Statement, will the temporary table be dropped? In other words, does a java.sql.Statement carry its own "session"? Thanks, Orhan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to obtain the code af a function ?
Hello, I would like to know how to liste the contain of a fonction. Writing \df in psql, I have a list of functions and triggers, but and don't know how to obtain the code of those functions. Thanks. C. Tobini ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postmaster failing to start on reboot
Hello everyone, I reboot my machine while postmaster is up. after the m/c gets rebooted , I grep for the process "postmaster", and it does not find it. So, I try starting postmaster. and it displays the following error message: HINT: If you're sure there are no old server processes still running, remove the shared memory block with the command "ipcrm", or just delete the file "/export/home1/sdc_image_pool/dbx/postmaster.pid". does it means that i will have to delete the postmaster.pid file ..in such a scenarion always? thanks, regards Surabhi Ahuja
[GENERAL] newbie design question re impact of VACUUM
After looking at "Chapter 22. Routine Database Maintenance Tasks" (http://www.postgresql.org/docs/8.1/interactive/maintenance.html), I started wondering about what (if any) consideration to give to to VACUUM issues in the following context. As a background, I'll be using Postgres in part as a processing queue for a 40-column stream of information (~ 250 bytes/row) with a sustained input rate of 20 rows/sec. This queue will be processed periodically (every few minutes), design constraints are to (1) only process each row once, and (2) keep the processed rows around for a period of time (say a month or so). My first (naive?) idea was to add a boolean "was_processed" column to the table (defaulted to false) and UPDATE it to true as part of (1). After reading Chapter 22, though, it seems that even a minor UPDATE like that copies the row and requires VACUUMing. Given that, my basic question is whether row width is a consideration in UPDATE or VACUUM performance, and if so if it is generally accepted practice to design around it? For example, if I were to make a child table to effectively hold the "was_processed" flag I could probably avoid UPDATEs entirely, but I'm not sure how to value that in this context. Thanks in advance for and help/info/pointers. ---(end of broadcast)--- TIP 1: 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
[GENERAL] Schemas shown in ODBC
Hi everyone, I've just upgraded from Postgres 8.0.1 to 8.1. For testing purposes I'm using a MS Access Frontend connected by ODBC. This worked fine until I switched to the new ODBC-Driver (the Unicode-version). When I try to connect to some new tables, the ODBC-Driver shows tables from the information_schema only. Adding a SET SEARCH_PATH specifing the public-schema explicitly doesn't help. The ANSI-driver won't do either. What's to be done, do have the choice what schema to connect with? Kind regards, Claus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to obtain the code af a function ?
Use \df+ instead of \df The additional "+" will give more details in many of the \ commands. Cheers, Csaba. On Wed, 2005-11-09 at 14:40, ctobini wrote: > Hello, > > I would like to know how to liste the contain of a fonction. > > Writing \df in psql, I have a list of functions and triggers, but and > don't know how to obtain the code of those functions. > > Thanks. > > C. Tobini > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: 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
[GENERAL] Question on Insert / Update
Hi, have just a general question... I have a table of 10M records, unique key on 5 fields. I need to update/insert 200k records in one go. I could do a select to check for existence and then either insert or update. Or simply insert, check on the error code an update if required. The 2nd seems to be to logical choice, but will it actually be faster and moreover is that the right way to do it? Thanks Alex ---(end of broadcast)--- TIP 1: 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: [GENERAL] How to obtain the code af a function ?
ctobini wrote: Hello, I would like to know how to liste the contain of a fonction. Writing \df in psql, I have a list of functions and triggers, but and don't know how to obtain the code of those functions. Start psql with -E and you'll see how all the backslash \d? commands work. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postmaster failing to start on reboot
surabhi.ahuja wrote: Hello everyone, I reboot my machine while postmaster is up. after the m/c gets rebooted , I grep for the process "postmaster", and it does not find it. So, I try starting postmaster. and it displays the following error message: HINT: If you're sure there are no old server processes still running, remove the shared memory block with the command "ipcr m", or just delete the file "/export/home1/sdc_image_pool/dbx/postmaster.pid". does it means that i will have to delete the postmaster.pid file ..in such a scenarion always? No, only when it doesn't close down properly. Check your system logs to see what happened. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to obtain the code af a function ?
am 09.11.2005, um 5:40:14 -0800 mailte ctobini folgendes: > Hello, > > I would like to know how to liste the contain of a fonction. You can find this in information_schema.routines. > > Writing \df in psql, I have a list of functions and triggers, but and > don't know how to obtain the code of those functions. Type \df+ . Hint: switch with \x HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] temporary tables
Orhan wrote: The PostgreSQL docs say that "Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction." If I create a temporary table through a java.sql.Statement and then call close() on that Statement, will the temporary table be dropped? In other words, does a java.sql.Statement carry its own "session"? A session is from connection to disconnect. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] newbie design question re impact of VACUUM
[EMAIL PROTECTED] wrote: After looking at "Chapter 22. Routine Database Maintenance Tasks" (http://www.postgresql.org/docs/8.1/interactive/maintenance.html), I started wondering about what (if any) consideration to give to to VACUUM issues in the following context. As a background, I'll be using Postgres in part as a processing queue for a 40-column stream of information (~ 250 bytes/row) with a sustained input rate of 20 rows/sec. This queue will be processed periodically (every few minutes), design constraints are to (1) only process each row once, and (2) keep the processed rows around for a period of time (say a month or so). I would have the application vacuum the queue table after processing a batch of rows. That's about as small as you could keep the table's disk usage. You are right in that updating the row will basically create a new version of the row. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question on Insert / Update
I guess the best solution is one which allows you to do it in batches, as inserting is more efficient if you don't commit after each insert. On Wed, 2005-11-09 at 15:45, Alex wrote: > Hi, > have just a general question... > > I have a table of 10M records, unique key on 5 fields. > I need to update/insert 200k records in one go. > > I could do a select to check for existence and then either insert or update. > Or simply insert, check on the error code an update if required. > The 2nd seems to be to logical choice, but will it actually be faster > and moreover is that the right way to do it? Are you aware that you can't do the 2nd in the same transaction ? The error will invalidate the current transaction. So you either do each insert in it's own transaction, basically with autocommit on, or you place a savepoint before each insert and roll back to it on the error, which is probably cheaper than commit but still not free. So I guess this is not good for batching. We here do all this kind of stuff by first looking up a batch of rows, then insert the missing/update the existing also in batch mode. In any case, I wonder too if it can't be done better, cause our imports are significantly slower on postgres than on other DBs (intentionally no names mentioned), using the same code and the machines having similar hardware. Cheers, Csaba. ---(end of broadcast)--- TIP 1: 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: [GENERAL] insert on duplicate update?
Richard Huxton writes: > Peter Filipov wrote: >> is there in postgres anything similar to: >> insert on duplicate update? >> here is the problem i need a solution for: >> I need to insert in pg_type a row which was previously selected from >> there and >> with slight modifications, the problem is that when i try to insert the row >> there might be a row with the same 'typname' which is sort of primary key. >> The second problem is that when doing those operations i don't want to >> depend >> on the specific structure of the pg_type catalog. > Are you certain it will always be safe to do this? What makes you think that modifying pg_type directly is a sane idea in the first place? When (not if) it breaks, you'll not get a lot of sympathy. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question on Insert / Update
am 10.11.2005, um 1:45:46 +1100 mailte Alex folgendes: > Hi, > have just a general question... > > I have a table of 10M records, unique key on 5 fields. > I need to update/insert 200k records in one go. > > I could do a select to check for existence and then either insert or > update. > Or simply insert, check on the error code an update if required. Take a look at http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] clustering by partial indexes
Quoting Tom Lane <[EMAIL PROTECTED]>:
> "Keith C. Perry" <[EMAIL PROTECTED]> writes:
> > This might have been discussed before but I wanted to know if clustering
> tables
> > by partial indexes will be availble in a later release of pgSQL?
>
> What in the world would it mean to do that?
I'm not sure I understand your question. Right now you can cluster tables based
on an index but when I tried to do that with a partial index I got an error.
That information was in my first email. Perhaps a more basic question why can't
I cluster by a partial index. Here is the information again cut & pasted from
pgadminIII v1.4 (I omitted the table ddl previously).
CREATE TABLE report
(
"match" int4 NOT NULL,
"action" varchar(16),
stamp timestamptz NOT NULL,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text,
CONSTRAINT report_pkey PRIMARY KEY ("match", stamp)
)
WITHOUT OIDS;
CREATE INDEX hrs_idx
ON report
USING btree
(stamp)
WHERE thehour(stamp) >= 0::double precision AND thehour(stamp) <= 23::double
precision;
CREATE OR REPLACE FUNCTION thehour(timestamptz)
RETURNS float8 AS
$BODY$
begin
return extract(hour from $1);
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Now when I go into the database with psql...
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
iprism=# \h cluster
Command: CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER
iprism=# cluster hrs_idx on report;
ERROR: cannot cluster on partial index "hrs_idx"
iprism=#
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] psql scripts
I have a file which is a long series of SQL commands. Creating tables, copying in data, indexing, munging the data, intermittently vacuuming to keep things moving. I have usually run this big script like this: nohup psql dbname < script.sql & After upgrading to 8.0, the script to slow to a crawl after a while. However, it works fine when run from inside a psql session like this \i script.sql All I can think of is that the entire script is running in one big transaction, although there are no explicit BEGIN statements in it. The man page for psql says, regarding the -c option "If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard input." implying that stdin is run exactly the same as \i. That being the case, what could be causing my script to slow to a crawl when redirected to psql, but run fine from inside psql with -i? - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question on Insert / Update
Quote from the link below: "Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need." I would think this places an automatic save-point at the begin of the block. I doubt this would give the best performance for the given problem... Cheers, Csaba. On Wed, 2005-11-09 at 16:20, A. Kretschmer wrote: > am 10.11.2005, um 1:45:46 +1100 mailte Alex folgendes: > > Hi, > > have just a general question... > > > > I have a table of 10M records, unique key on 5 fields. > > I need to update/insert 200k records in one go. > > > > I could do a select to check for existence and then either insert or > > update. > > Or simply insert, check on the error code an update if required. > > Take a look at > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > > > HTH, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Troubles with array_ref
Thanks a lot man!!! You saved my life :P -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Martes, 08 de Noviembre de 2005 04:13 p.m. To: Cristian Prieto Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [GENERAL] [HACKERS] Troubles with array_ref "Cristian Prieto" <[EMAIL PROTECTED]> writes: > Well, anyway, this is the Stored Function I've been workin on; it simply > take an array and an integer just to return this item from the array; The > array could have any kind of elements so I declare it as anyarray (the > parameter) and anyelement (the return value), please help me, I don't know > where to get info about it. You could save yourself a lot of time if you enabled warnings from your C compiler (eg, -Wall for gcc) and then paid some attention to them. The last parameter to array_ref is a bool *, not a bool, and I have no doubt that the backend is crashing while trying to dereference "false". (Another problem is that the fourth parameter should be -1 not VARSIZE.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] clustering by partial indexes
"Keith C. Perry" <[EMAIL PROTECTED]> writes: > Quoting Tom Lane <[EMAIL PROTECTED]>: >> "Keith C. Perry" <[EMAIL PROTECTED]> writes: >>> This might have been discussed before but I wanted to know if clustering >>> tables by partial indexes will be availble in a later release of pgSQL? >> >> What in the world would it mean to do that? > I'm not sure I understand your question. CLUSTER says "order the table according to the order of the entries in this index". A partial index doesn't define an ordering for the whole table, only the rows that have entries in that index. So it doesn't seem to me that you are asking for something that has a well defined meaning. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postmaster failing to start on reboot
Richard Huxton writes: > surabhi.ahuja wrote: >> So, I try starting postmaster. and it displays the following error >> message: HINT: If you're sure there are no old server processes >> still running, remove the shared memory block with the command "ipcr >> m", or just delete the file >> "/export/home1/sdc_image_pool/dbx/postmaster.pid". >> >> does it means that i will have to delete the postmaster.pid file ..in >> such a scenarion always? > No, only when it doesn't close down properly. Check your system logs to > see what happened. Also, what PG version is this exactly? More recent versions have better defenses against being fooled by stale postmaster.pid files. It matters what postmaster startup script you're using, too. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Beyond the 1600 columns limit on windows
Yes it is exactly that. I will follow you advice and create a abstraction layer for the data access that will return the sparse dataset using the standard dataset as input. There is just one thing I disagree you said it that the performance is not good, right. However, it is practical! Nothing is easier and more practical than keeping the sparse representation inside of the database for my application. On 11/8/05, John D. Burger <[EMAIL PROTECTED]> wrote: Evandro's mailing lists (Please, don't send personal messages to thisaddress) wrote:> It has nothing to do with normalisation. It is a program for > scientific applications.> Datavalues are broken into column to allow multiple linear regression> and multivariate regression trees computations.Having done similar things in the past, I wonder if your current DB design includes a column for every feature-value combination:instanceID color=red color=blue color=yellow ... height=71height=72-42 True False False 43 False TrueFalse44 False False True...This is likely to be extremely sparse, and you might use a sparserepresentation accordingly. As several folks have suggested, the representation in the database needn't be the same as in your code.> Even SPSSthe most well-known statistic sw uses the same approach and> data structure that my software uses.> Probably I should use another data structure but would not be as > eficient and practical as the one I use now.The point is that, if you want to use Postgres, this is not in factefficient and practical. In fact, it might be the case that mappingfrom a sparse DB representation to your internal data structures is =more= efficient than naively using the same representation in bothplaces.- John D. Burger MITRE-- Evandro M Leite JrPhD Student & Software developer University of Southampton, UKPersonal website: http://evandro.orgAcademic website: http://www.soton.ac.uk/~evandroPlease, use Jr(at)evandro.org for personal messages
Re: [GENERAL] psql scripts
Ian Harding <[EMAIL PROTECTED]> writes: > That being the case, what could be causing my script to slow to a > crawl when redirected to psql, but run fine from inside psql with -i? Readline issue maybe? Please try this way psql -f script.sql as well as the stdin way; then try both with -n (no readline) added. Also, what platform is this on, and PG 8.0.whatexactly? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] insert on duplicate update?
You absolutely should NOT I repeat NOT -- DON'T dink around with the system catalogs in ANY database... If you need to create UDT's then you should follow the proper mechanisms to create your own user-defined types... ""Peter Filipov"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > hi, > is there in postgres anything similar to: > insert on duplicate update? > here is the problem i need a solution for: > I need to insert in pg_type a row which was previously selected from there > and > with slight modifications, the problem is that when i try to insert the > row > there might be a row with the same 'typname' which is sort of primary key. > The second problem is that when doing those operations i don't want to > depend > on the specific structure of the pg_type catalog. I want my procedure > to work even if in some future release additional columns to pg_type are > added. > So what i want is to insert a row in a table that was previously selected > from the same > table but if there is primary key collision i want to update the row that > collided. And > all these without being dependant on the table structure. > > -- > Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Yes it is exactly that. I will follow you advice and create a abstraction layer for the data access that will return the sparse dataset using the standard dataset as input. There is just one thing I disagree you said it that the performance is not good, right. However, it is practical! Nothing is easier and more practical than keeping the sparse representation inside of the database for my application. I bet even your application would profit from not handling sparse data. You could just "not" insert them into your tree instead of having to jump over empty elements. And there is always a way to lazily abstract the data to some frontend (While I doubt anybody can actuall scroll wide enough on a screen to see all the 1600 colums ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] newbie design question re impact of VACUUM
In article <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > As a background, I'll be using Postgres in part as a processing queue > for a 40-column stream of information (~ 250 bytes/row) with a > sustained input rate of 20 rows/sec. This queue will be processed > periodically (every few minutes), design constraints are to (1) only > process each row once, and (2) keep the processed rows around for a > period of time (say a month or so). > My first (naive?) idea was to add a boolean "was_processed" column to > the table (defaulted to false) and UPDATE it to true as part of (1). > After reading Chapter 22, though, it seems that even a minor UPDATE > like that copies the row and requires VACUUMing. That's true, but there might be a way to avoid it. If your queue elements have a timestamp, you could run your processing routine not over elements where "was_processed" is false, but over elements within some time interval, e.g. the last minute. This would eliminate the need for an UPDATE. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PostgreSQL now() function returns incorrect time
I was wondering if somebody could point me in the right direction. I'm situated in Brazil and recently daylights savings/summer time kicked in and clocks were set one hour ahead. Now on my windows box the system clock automatically adjusted to the correct time. But performing the following query on the postgresql-8.0 server i get this result: select now() -- 2005-11-09 13:22:27.625-03 while the actual current time is 14:22h, and being reported correctly in the windows system clock I have tried finding some information in the documentation and came across locale settings in the postgresql.conf configuration file but Im not sure if this is the correct place to be looking. Should i set the timezone property in postgresql.conf to something other than unknown? any help would be appreciated, giovanni-- A World of KEIGIhttp://keigi.blogspot.com
Re: [GENERAL] resetting superuser password
I would unix su, edit pg_hba.conf to allow open access temporarily, connect to pg and change the posgres password. Don't forget to change pg_hba.conf back again to password protect the db! TJ I have lost the superuser (user postgres) password, but I still have the unix root password. Is there anything I can do to reset the postgres user's password (short of reinstalling Postgres)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL now() function returns incorrect time
On Wed, Nov 09, 2005 at 02:24:28PM -0200, Giovanni M. wrote: > I'm situated in Brazil and recently daylights savings/summer time kicked in > and clocks were set one hour ahead. Now on my windows box the system clock > automatically adjusted to the correct time. But performing the following > query on the postgresql-8.0 server i get this result: > select now() > -- > 2005-11-09 13:22:27.625-03 > while the actual current time is 14:22h, and being reported correctly in > the windows system clock > I have tried finding some information in the documentation and came across > locale settings in the postgresql.conf configuration file but Im not sure if > this is the correct place to be looking. Should i set the timezone property > in postgresql.conf to something other than unknown? Do you see the correct time if you do the following? SET timezone TO 'Brazil/East'; SELECT now(); If so then change the timezone line in postgresql.conf to: timezone = Brazil/East Then reload or restart the database. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] libpq version in rpm packages
I just set up a new server and would like to use rpms to manage the software on this one. I've done the compile from source thing most of the time, but over time it seems to get messy. I'm using CentOS 4.2, which only has packages for postgres 7.4, but I very much want to use 8.1. I installed the 8.1 RPMs, but when I try to install packages like perl-DBD-Pg, or php-pgsql, rpm complains because they want libpq.so.3, and postgres 8.1 comes with libpq.so.4. Does anyone have a workaround for this? I've thought about forcing the rpms to install, or even installing the postgres libs from 7.4. Suggestions? Thanks.
[GENERAL] Where Statement
The following transfers the serial number of fluid_id into the two tables, specification and pipe. However, it doesn't follow the WHERE instruction. All fluid_id serial numbers in process are transferred to fluid_id column in pipe with no discrimination. I can't see what I have done wrong. Bob create or replace function base() returns trigger as $$ begin insert into specification (fluid_id) values (new.fluid_id); create table pro as select fluid_id from process where ip_op_reactor = 'ip'; insert into pipe (fluid_id) values (new.fluid_id); drop table pro ; return null; end; $$ language plpgsql; create trigger trig1 after insert on process for each row execute procedure base();
[GENERAL] using new bitmap scans to index bit columns?
I like the new bitmap scans and I'm wondering if there is any way I can utilize them for my bitmask column, defined as bit(1024). I use this column as a sort of fingerprint to quickly scan my tables. But it is a scan, not an index. I have not figured out a way to index the bitmask column. Is there some way it can be used as an index now that there are bitmap scans in 8.1? Currently I do this: Select * from mytable where contains(bitmask, fingerprint(user_data)) and matches(datacolumn, user_data); user_data is a string, like a regexp but with different semantics for chemical data. bitmask is precomputed/stored as bit(1024) = fingerprint(datacolumn) contains(a,b) returns bool as 'select b=(a&b);' This works well because matches() is an expensive functions. But it would work better if bitmask could be indexed, no? TJ O'Donnell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Hanging creating of function
Can anyone explain why the first one never completes, but the second one does? (the first one just keeps running, I canceled after ~1 min) PG version: 8.1 final -- tblname param has type varchar create or replace function getcolstring (tblname varchar) returns varchar as $$ declare table_columns varchar := ''; column_name record; begin for column_name in select pga.attname from pg_attribute pga, pg_class pgc where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop table_columns := table_columns || column_name.attname || ','; end loop; -- chop the last ',' table_columns := substr(table_columns,1,(length(table_columns)-1)); return table_columns; end; $$ language plpgsql; -- tblname param has type text create or replace function getcolstring (tblname text) returns varchar as $$ declare table_columns varchar := ''; column_name record; begin for column_name in select pga.attname from pg_attribute pga, pg_class pgc where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop table_columns := table_columns || column_name.attname || ','; end loop; -- chop the last ',' table_columns := substr(table_columns,1,(length(table_columns)-1)); return table_columns; end; $$ language plpgsql; /Mikael ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Where Statement
On Wed, Nov 09, 2005 at 10:06:06AM -0800, Bob Pawley wrote: > The following transfers the serial number of fluid_id into the two > tables, specification and pipe. > > However, it doesn't follow the WHERE instruction. All fluid_id serial > numbers in process are transferred to fluid_id column in pipe with no > discrimination. Could you post a minimal but complete example with all create, insert, and select statements necessary to reproduce the problem? What version of PostgreSQL are you running? What's the purpose of "create table pro as" when you drop it without doing anything? Or does the actual code do something before the drop? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] using new bitmap scans to index bit columns?
On Wed, 9 Nov 2005, TJ O'Donnell wrote: I like the new bitmap scans and I'm wondering if there is any way I can utilize them for my bitmask column, defined as bit(1024). I use this column as a sort of fingerprint to quickly scan my tables. But it is a scan, not an index. I have not figured out a way to index the bitmask column. Is there some way it can be used as an index now that there are bitmap scans in 8.1? Currently I do this: Select * from mytable where contains(bitmask, fingerprint(user_data)) and matches(datacolumn, user_data); user_data is a string, like a regexp but with different semantics for chemical data. bitmask is precomputed/stored as bit(1024) = fingerprint(datacolumn) contains(a,b) returns bool as 'select b=(a&b);' This works well because matches() is an expensive functions. But it would work better if bitmask could be indexed, no? You can use GiST to do that. TJ O'Donnell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: 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
[GENERAL] Foreign key reference causes invalid DELETE trigger calls
I need to log table "foo" real deletes.
"foo" has foreign key relation established but no data exists.
Postgres 8.1 calls "foo" delete trigger when record is deleted from master
table "klient". Why ?
How to modify the following code so that record is inserted into serveri
table only when records are really deleted from foo table ?
Is it possible to add some check into trigger code?
CREATE TABLE serverti ( notice char(50));
CREATE FUNCTION setlastchange() RETURNS "trigger" AS $$
BEGIN
INSERT INTO serverti values ('changed');
RETURN NULL;
END$$ LANGUAGE plpgsql;
CREATE table klient ( kood integer primary key );
CREATE TABLE foo (
klient char(12) NOT NULL,
toode char(20) NOT NULL,
CONSTRAINT foo_pkey PRIMARY KEY (klient, toode),
CONSTRAINT foo_klient_fkey FOREIGN KEY (klient)
REFERENCES klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
) ;
CREATE TRIGGER foo_trig
BEFORE INSERT OR UPDATE OR DELETE ON foo
FOR EACH STATEMENT
EXECUTE PROCEDURE setlastchange();
insert into klient values (1);
-- Next line causes execution of foo_trig. Why ?
delete from klient where kood=1;
Andrus.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] using new bitmap scans to index bit columns?
On Wed, Nov 09, 2005 at 10:18:35AM -0800, TJ O'Donnell wrote: > I like the new bitmap scans and I'm wondering if there is any way > I can utilize them for my bitmask column, defined as bit(1024). > I use this column as a sort of fingerprint to quickly scan my tables. > But it is a scan, not an index. I have not figured out a way to > index the bitmask column. Is there some way it can be used as > an index now that there are bitmap scans in 8.1? Note: the fact that they are called bitmaps indexes doesn't imply anything about the types used. The fact that you have a field already as a bitmap doesn't actually help. The operation still needs to be an indexable. However, bitmaps indexes does mean that a single query can use multiple indexes. So if you can split your contains into different parts of the string, the optimozier can combine them. Whether this is more efficient, who knows... Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp8tNoatpt8B.pgp Description: PGP signature
Re: [GENERAL] Hanging creating of function
On Wed, Nov 09, 2005 at 07:38:53PM +0100, Mikael Carneholm wrote: > Can anyone explain why the first one never completes, but the second > one does? (the first one just keeps running, I canceled after ~1 min) > > PG version: 8.1 final Both functions create fine here in 8.1.0 on FreeBSD 6.0/i386 and Solaris 9/sparc. What client are you using? If not psql, have you tried with psql? What platform are you on? -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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: [GENERAL] Hanging creating of function
"Mikael Carneholm" <[EMAIL PROTECTED]> writes: > Can anyone explain why the first one never completes, but the second > one does? They both work fine for me ... regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] Foreign key reference causes invalid DELETE trigger calls
"Andrus" <[EMAIL PROTECTED]> writes: > Postgres 8.1 calls "foo" delete trigger when record is deleted from master > table "klient". Why ? Because you have an ON DELETE CASCADE. That leads to a DELETE ... WHERE ... on the slave table; whether any records actually get deleted depends on what the DELETE finds. This is a general hazard of using STATEMENT triggers: you have no info about whether the statement actually did anything. (It's rather silly to imagine that a BEFORE STATEMENT trigger would have any way to know that anyway. We currently don't tell an AFTER STATEMENT trigger anything either; though that may change someday.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Hanging creating of function
Postgres machine OS: RHEL release 2.1 (Panama) PGAdminIII client OS: WinXP Pro 2002 SP2 Clients: psql, PGAdminIII Query tool dfol=> select version(); version - PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC 2.96 The original versions looked like this (same behaviour though): create or replace function dfol.getcolstring (tblname varchar) returns varchar as $$ declare table_columns text; column_name record; begin -- skapa kolumnnamns-strängarna for column_name in select pga.attname from pg_attribute pga, pg_class pgc where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop table_columns := table_columns || column_name.attname || ','; end loop; -- kapa av sista kommatecknet table_columns := substring(table_columns from 1 for length(table_columns)-1); return table_columns; end; $$ language plpgsql; create or replace function dfol.getcolstring (tblname text) returns varchar as $$ declare table_columns text; column_name record; begin -- skapa kolumnnamns-strängarna for column_name in select pga.attname from pg_attribute pga, pg_class pgc where pga.attrelid = pgc.relfilenode and pgc.relname = tblname and pga.attnum > 0 loop table_columns := table_columns || column_name.attname || ','; end loop; -- kapa av sista kommatecknet table_columns := substring(table_columns from 1 for length(table_columns)-1); return table_columns; end; $$ language plpgsql; -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: den 9 november 2005 19:56 To: Mikael Carneholm Cc: 'Pgsql-General (E-mail) Subject: Re: [GENERAL] Hanging creating of function On Wed, Nov 09, 2005 at 07:38:53PM +0100, Mikael Carneholm wrote: > Can anyone explain why the first one never completes, but the second > one does? (the first one just keeps running, I canceled after ~1 min) > > PG version: 8.1 final Both functions create fine here in 8.1.0 on FreeBSD 6.0/i386 and Solaris 9/sparc. What client are you using? If not psql, have you tried with psql? What platform are you on? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] libpq version in rpm packages
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 9 Nov 2005, Brian Mathis wrote: I'm using CentOS 4.2, which only has packages for postgres 7.4, but I very much want to use 8.1. I installed the 8.1 RPMs, but when I try to install packages like perl-DBD-Pg, or php-pgsql, rpm complains because they want libpq.so.3, and postgres 8.1 comes with libpq.so.4. Does anyone have a workaround for this? I've thought about forcing the rpms to install, or even installing the postgres libs from 7.4. We have a compat RPM to solve that issue: http://developer.postgresql.org/~devrim/rpms/compat/ There are RPMs for x86 and x86_64; as well as a srpm. This RPM includes libs from older PostgreSQL versions and these libs satisfy the dependencies. Regards, - -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFDckwF4zE8DGqpiZARAvUvAKCfhBwx1OJ3KrpCBU1ODTnBaFwb3ACgqPdO OJ9hxlkksO7dHtAy1rd/tDo= =mcse -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question on Insert / Update
On 11/9/05 9:45 AM, "Alex" <[EMAIL PROTECTED]> wrote: > Hi, > have just a general question... > > I have a table of 10M records, unique key on 5 fields. > I need to update/insert 200k records in one go. > > I could do a select to check for existence and then either insert or update. > Or simply insert, check on the error code an update if required. > > The 2nd seems to be to logical choice, but will it actually be faster > and moreover is that the right way to do it? Probably the fastest and most robust way to go about this if you have the records in the form of a tab-delimited file is to COPY or \copy (in psql) them into a separate loader table and then use SQL to manipulate the records (check for duplicates, etc) for final insertion into the table. Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to use indexes for partial match at beginning
Martijn,
>> I can use queries:
>>
>> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)
>Well, you could do that in postgresql too, you just need to use the SQL
>standard concatination operator.
>WHERE col1 BETWEEN 'f' and 'f' || chr(255);
thank you.
I think it is best to use regular indexes since regular indexes since they
can be used in other types of queries also.
It seems that only way is to use BETWEEN comparison for this in Postgres
8.1.
I tried
CREATE TABLE foo ( col1 CHAR(20));
CREATE INDEX i1 ON foo(col1);
INSERT INTO foo VALUES ('bar');
SELECT * FROM foo WHERE col1 BETWEEN 'b' and 'b' || chr(255);
But this does not return any data.
How to write index optimizable WHERE clause when only some (variable number)
of characters from beginning of col1 are known ?
Only way seems to use BETWEEN comparison by concatenating character greater
than all other characters in locale. Since CHR(255) does not work this is
not possible.
So
CREATE INDEX i1 ON foo(col1);
cannot be used to optimize queries of type "get all rows where first n
charaters of col1 are known" in Postgres.
Andrus.
---(end of broadcast)---
TIP 1: 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
[GENERAL] invalid UTF-8 byte sequence detected
Hello! I am currently testdriving migration of our PostgreSQL 8.0 databases to 8.1; in this process I have stumbled a couple of times over certain errors in text-fields that lead to error-messages during import of the dump like these: <2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]>ERROR: invalid UTF-8 byte sequence detected near byte 0xb4 <2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]>CONTEXT: COPY board_message, line 1125662, column text: "HI Besteht ein gewisser Nachteil, wenn ich nur eins von den beiden kaufe, da in beiden Addon▒s viel..." <2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]>STATEMENT: COPY board_message (board_id, thread_id, father_id, message_id, user_id, title, signat ure, follow_up, count_reply, last_reply, created, article_id, logged_ip, state_id, user_login, user_status, user_rank, user_rank_description, user_rank_picture, user_rights, text, deleted_user_id, deleted_date, deleted_login, user_created, poll_id, idxfti) FROM stdin; <2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]>ERROR: invalid UTF-8 byte sequence detected near byte 0x98 <2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]>CONTEXT: COPY kidszone_tournament2005_user, line 427, column phone: "02302▒74" <2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]>STATEMENT: COPY kidszone_tournament2005_user (id, first_name, last_name, adress, birthday, phone, email, place, permission, ude, ude_box, invited) FROM stdin; There are not too many occurrences of the same type - five altogether in a 1.8GB compressed dumpfile, but still it has me worried and leaves me with some questions: 1.) How could I have prevented insertion of these invalid byte-sequences in the first place? We're using UTF-8 encoded databases, data is mostly inserted by users via browser applications, our websites are UTF-8 encoded, too, but still we cannot really make 100% sure that all clients behave as expected; on the other hand, it would be extremely inconvenient if we had to check each and every text input for character set conformance in the application, so is there a way to ascertain "sane" data via some database-setting? pg_restore does throw this error and indeed terminates after that (I used custom dump format for pg_dump), psql on the other hand just continues with the import (using a pgdumpall-output that generates a standard SQL-script), although it too throws the error. 2.) How does this really affect the value of the database-dumps? psql continues with import after the error, but the table where this error occurred remains empty, as the affected COPY-statement has failed altogether due to this error. So a plain no-worries import in my case would present me a result with five tables empty - one of them quite large... Is there some kind of magic, maybe involving some perl or whatever, that could help to clean up the dump before the import, so I can accomplish a full restore? Kind regards, Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best way to use indexes for partial match at beginning
Andrus wrote: So CREATE INDEX i1 ON foo(col1); cannot be used to optimize queries of type "get all rows where first n charaters of col1 are known" in Postgres. Of course it will! Any btree based index will let you do that. Re-read the previous answers and make sure you pay attention to the bit about text_pattern_ops and LIKE in non-C locales. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best way to use indexes for partial match at beginning
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote:
> Martijn,
>
> >> I can use queries:
> >>
> >> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)
>
> >Well, you could do that in postgresql too, you just need to use the SQL
> >standard concatination operator.
>
> >WHERE col1 BETWEEN 'f' and 'f' || chr(255);
>
> thank you.
>
> I think it is best to use regular indexes since regular indexes since they
> can be used in other types of queries also.
>
> It seems that only way is to use BETWEEN comparison for this in Postgres
> 8.1.
>
> I tried
>
> CREATE TABLE foo ( col1 CHAR(20));
> CREATE INDEX i1 ON foo(col1);
> INSERT INTO foo VALUES ('bar');
> SELECT * FROM foo WHERE col1 BETWEEN 'b' and 'b' || chr(255);
>
> But this does not return any data.
>
> How to write index optimizable WHERE clause when only some (variable number)
> of characters from beginning of col1 are known ?
>
> Only way seems to use BETWEEN comparison by concatenating character greater
> than all other characters in locale. Since CHR(255) does not work this is
> not possible.
>
> So
>
> CREATE INDEX i1 ON foo(col1);
>
> cannot be used to optimize queries of type "get all rows where first n
> charaters of col1 are known" in Postgres.
>
> Andrus.
>
>
you can create two indexes:
CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops);
and
CREATE INDEX myindex_normal ON foo(col1);
the first one will be used when using LIKE and the other for normal
comparisons .
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] libpq version in rpm packages
Thanks for that rpm. I noticed that the x86_64 version of the compat libs installs the files in /usr/lib, while the x86_64 postgresql-libs rpm uses /usr/lib64. Is there are reason for that? Otherwise, I'd prefer to keep all the 64bit libs in lib64. On 11/9/05, Devrim GUNDUZ <[EMAIL PROTECTED]> wrote: We have a compat RPM to solve that issue:http://developer.postgresql.org/~devrim/rpms/compat/There are RPMs for x86 and x86_64; as well as a srpm. This RPM includes libs from older PostgreSQL versions and these libs satisfy thedependencies.Regards,- --Devrim GUNDUZKivi Bilişim Teknolojileri - http://www.kivi.com.tr
Re: [GENERAL] PostgreSQL now() function returns incorrect time
I followed your instructions and now() indeed returned the correct time! Thanks for your help On 11/9/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Wed, Nov 09, 2005 at 02:24:28PM -0200, Giovanni M. wrote:> I'm situated in Brazil and recently daylights savings/summer time kicked in > and clocks were set one hour ahead. Now on my windows box the system clock> automatically adjusted to the correct time. But performing the following> query on the postgresql-8.0 server i get this result: > select now()> --> 2005-11-09 13:22:27.625-03> while the actual current time is 14:22h, and being reported correctly in> the windows system clock> I have tried finding some information in the documentation and came across > locale settings in the postgresql.conf configuration file but Im not sure if> this is the correct place to be looking. Should i set the timezone property> in postgresql.conf to something other than unknown? Do you see the correct time if you do the following?SET timezone TO 'Brazil/East';SELECT now();If so then change the timezone line in postgresql.conf to:timezone = Brazil/EastThen reload or restart the database. --Michael Fuhr-- A World of KEIGIhttp://keigi.blogspot.com
Re: [GENERAL] Foreign key reference causes invalid DELETE trigger calls
> Because you have an ON DELETE CASCADE. That leads to a DELETE ... WHERE > ... > on the slave table; whether any records actually get deleted depends on > what the DELETE finds. This is a general hazard of using STATEMENT > triggers: you have no info about whether the statement actually did > anything. Tom, thank you. I try to implement table level replication in my application. Last table change time is written to control table by trigger. Client finds the tables which are changed after last login and re-loads the whole tables. Unfortunately, ON DELETE CASCADE tables are replicated always in this case which slows down my application startup. Should I use foo_trig as row level trigger to detect only real row deletion ? > (It's rather silly to imagine that a BEFORE STATEMENT trigger > would have any way to know that anyway. We currently don't tell an > AFTER STATEMENT trigger anything either; though that may change > someday.) I tried following code: insert into klient values (1); update klient set kood=2 WHERE kood=3; In this case, foo_trig trigger is NOT executed. So BEFORE UPDATE ROW trigger is NOT executed when there are no rows to update, but BEFORE DELETE ROW trigger IS executed when there are no rows to delete! Why is this behaviour inconsistent ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] newbie design question re impact of VACUUM
First rule of performance tuning: don't. See how well things run using the simple plan you've drawn up. If performance is acceptable, you're done. Yes, you could keep the flag in a seperate table, but remember that every row has a ~20 byte overhead, which is non-trivial. If you want to go this route, I suggest keeping a table of queue entries yet to be processed (can probably just be a table of ints). To see what you need to process, join that table back to the main queue table. When you've processed something, delete it's row from the 'side table'. Another possibility is to have two tables; one is the active queue and the other is historical. This is probably a better way to go so that your queue process doesn't have to slog through months of data. After you've processed an entry, just move it from the active table to the history table. On Tue, Nov 08, 2005 at 08:09:09AM -0800, [EMAIL PROTECTED] wrote: > After looking at "Chapter 22. Routine Database Maintenance Tasks" > (http://www.postgresql.org/docs/8.1/interactive/maintenance.html), I > started wondering about what (if any) consideration to give to to > VACUUM issues in the following context. > > As a background, I'll be using Postgres in part as a processing queue > for a 40-column stream of information (~ 250 bytes/row) with a > sustained input rate of 20 rows/sec. This queue will be processed > periodically (every few minutes), design constraints are to (1) only > process each row once, and (2) keep the processed rows around for a > period of time (say a month or so). > > My first (naive?) idea was to add a boolean "was_processed" column to > the table (defaulted to false) and UPDATE it to true as part of (1). > After reading Chapter 22, though, it seems that even a minor UPDATE > like that copies the row and requires VACUUMing. Given that, my basic > question is whether row width is a consideration in UPDATE or VACUUM > performance, and if so if it is generally accepted practice to design > around it? For example, if I were to make a child table to effectively > hold the "was_processed" flag I could probably avoid UPDATEs entirely, > but I'm not sure how to value that in this context. > > Thanks in advance for and help/info/pointers. > > > ---(end of broadcast)--- > TIP 1: 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 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] libpq version in rpm packages
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 9 Nov 2005, Brian Mathis wrote: I noticed that the x86_64 version of the compat libs installs the files in /usr/lib, while the x86_64 postgresql-libs rpm uses /usr/lib64. Is there are reason for that? Otherwise, I'd prefer to keep all the 64bit libs in lib64. Good catch! It seems that I used an errorneus .rpmmacros file in the x86_64 server :( The RPM is fixed now and will be uploaded in an hour. Until then, you can do a rpmbuild --rebuild with the srpm and build the correct RPM for yourself. Regards, - -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFDcl5S4zE8DGqpiZARAn/PAJ9zsfpK7Z85fMgGzqB8nedmiYFCswCgkyWU H9nHr7MruNoResv+S8YBWdU= =MfHS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best way to use indexes for partial match at beginning
> you can create two indexes: > > CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops); > and > CREATE INDEX myindex_normal ON foo(col1); > > the first one will be used when using LIKE and the other for normal > comparisons . Jaime, CREATE INDEX myindex_normal ON foo(col1); Creates btree structure. In other dbm system btree structure can be used for searches where only some first characters in index key are known. So I see no reason to create second index using text_pattern_ops for this purpose. I'm searching a way to use Postgres regular index for this. Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best way to use indexes for partial match at beginning
>> CREATE INDEX i1 ON foo(col1); >> >> cannot be used to optimize queries of type "get all rows where first n >> charaters of col1 are known" in Postgres. > > Of course it will! Any btree based index will let you do that. Re-read the > previous answers and make sure you pay attention to the bit about > text_pattern_ops and LIKE in non-C locales. Richard, thank you. I try to formulate my problem more presicely. I have table CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY); Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1 PRIMARY KEY clause creates btree based index so the index exists on bar. I want to run fast queries by knowing first characters of bar like : 1. Select records from foo where first character of bar is A 2. Select records from foo where first character of bar is B 3. Select records from foo where first two characters of bar are BC 4. Select records from foo where first three characters of bar are ABC etc. Can you write sample WHERE clause which can use existing primary key index for those queries ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best way to use indexes for partial match at
On Wed, 2005-11-09 at 14:56, Andrus wrote: > > you can create two indexes: > > > > CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops); > > and > > CREATE INDEX myindex_normal ON foo(col1); > > > > the first one will be used when using LIKE and the other for normal > > comparisons . > > Jaime, > > CREATE INDEX myindex_normal ON foo(col1); > > Creates btree structure. In other dbm system btree structure can be used for > searches where only some first characters in index key are known. > > So I see no reason to create second index using text_pattern_ops for this > purpose. > > I'm searching a way to use Postgres regular index for this. Easy, do what those other databases do. Setup your database to not use a locale. initdb --locale=C and you're golden. ---(end of broadcast)--- TIP 1: 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: [GENERAL] Best way to use indexes for partial match at beginning
On Wed, Nov 09, 2005 at 10:46:27PM +0200, Andrus wrote: > thank you. I try to formulate my problem more presicely. > I have table > > CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY); > > Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1 Do this instead: CREATE TABLE foo ( bar CHAR(10) NOT NULL ); CREATE UNIQUE INDEX foo_bar ON foo(bar char_pattern_ops); > I want to run fast queries by knowing first characters of bar like : > > 1. Select records from foo where first character of bar is A > 2. Select records from foo where first character of bar is B > 3. Select records from foo where first two characters of bar are BC > 4. Select records from foo where first three characters of bar are ABC SELECT * FROM foo WHERE bar LIKE 'A%'; SELECT * FROM foo WHERE bar LIKE 'B%'; SELECT * FROM foo WHERE bar LIKE 'BC%'; SELECT * FROM foo WHERE bar LIKE 'ABC%'; Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp1qPlHjZAfA.pgp Description: PGP signature
Re: [GENERAL] Best way to use indexes for partial match at beginning
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote: > > you can create two indexes: > > > > CREATE INDEX myindex_lc on mytable(col1 text_pattern_ops); > > and > > CREATE INDEX myindex_normal ON foo(col1); > > > > the first one will be used when using LIKE and the other for normal > > comparisons . > > Jaime, > > CREATE INDEX myindex_normal ON foo(col1); > > Creates btree structure. In other dbm system btree structure can be used for > searches where only some first characters in index key are known. > and the same is true for postgres when you use C LOCALE, but because some implementation details i don't know so deep when using non-C LOCALE you need the class operator in order to use the index with LIKE 'pattern%' > So I see no reason to create second index using text_pattern_ops for this > purpose. > the reason is that you want to use the index in the search... and, at least you go and solve the problem with code, that's the way to do it... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best way to use indexes for partial match at beginning
On 2005-11-09 13:08, Martijn van Oosterhout wrote: I want to run fast queries by knowing first characters of bar like : 1. Select records from foo where first character of bar is A 2. Select records from foo where first character of bar is B 3. Select records from foo where first two characters of bar are BC 4. Select records from foo where first three characters of bar are ABC SELECT * FROM foo WHERE bar LIKE 'A%'; SELECT * FROM foo WHERE bar LIKE 'B%'; SELECT * FROM foo WHERE bar LIKE 'BC%'; SELECT * FROM foo WHERE bar LIKE 'ABC%'; Have a nice day, Or: SELECT * FROM foo WHERE bar::CHAR(1) = 'A'; SELECT * FROM foo WHERE bar::CHAR(1) = 'B'; SELECT * FROM foo WHERE bar::CHAR(2) = 'BC'; SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC'; -- Dean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] libpq version in rpm packages
Brian Mathis wrote: I just set up a new server and would like to use rpms to manage the software on this one. I've done the compile from source thing most of the time, but over time it seems to get messy. I'm using CentOS 4.2, which only has packages for postgres 7.4, but I very much want to use 8.1. I installed the 8.1 RPMs, but when I try to install packages like perl-DBD-Pg, or php-pgsql, rpm complains because they want libpq.so.3, and postgres 8.1 comes with libpq.so.4. Does anyone have a workaround for this? I've thought about forcing the rpms to install, or even installing the postgres libs from 7.4. I did a rpmbuild --rebuld of those two packages (perl and php) after I installed 8.1. This worked OK, but I can't run a nightly yum update because it complains that it can't install the update for the php package. Is there some type of compat rpm that we can install so that these packages will find the required version of libpq? Matt ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign key reference causes invalid DELETE trigger calls
"Andrus" <[EMAIL PROTECTED]> writes: > I tried following code: > insert into klient values (1); > update klient set kood=2 WHERE kood=3; > In this case, foo_trig trigger is NOT executed. Well, yes, because that UPDATE didn't change any rows of the master table. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] set-valued function difference in 8.1.0 vs 8.0.2
I just wanted to make sure this change in behavior is
a feature and not a bug.
This block of code with a (mis?)use of a set-valued function:
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$ {
my $res = [];
push @$res,'a';
push @$res,'b';
return $res;
} $$ LANGUAGE plperl STABLE;
select * from foo();
select foo();
worked "fine" in 8.0.2 but gives an
ERROR: set-valued function called in context that cannot accept a set
error in 8.1.0.
The 8.0.2 behavior of expanding the set-valued function when used
in the left part of the select clause was convenient in some
functions where I had used it like this:
select addr,geocode_alternatives(addr) from (
select addr from table where ambiguous=true
) as a;
where geocode_alternatives was a set-valued function that returned
all the alternatives for the ambiguous addresses.
Basically the results with 8.0.2 were something like:
addr | geocode_alternative
---+
1 main st | 1 N main st
1 main st | 1 S main st
1 main st | 1 main ave
30 mlk dr | 2 Martin Luther King dr
30 mlk dr | 2 milk dr
And now I'm having a hard time coming up with a way of
re-writing it without a similar error. Is there an
easy way of rewriting this construct where the results
of a function can expand the result set that works
nicely in 8.1?
Thanks,
Ron
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] build errors on MacOSX
My build of postgresql-8.1.0 is failing on MacOSX. Following the INSTALL file, I passed configure flags for bonjour (what's that?) and python support. i.e.: ./configure --with-bonjour --with-python My invocation of make, launched from the postgres account, returns the following errors: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Winline -Wendif-labels -fno-strict-aliasing -dynamiclib - install_name /usr/local/pgsql/lib/libpq.4.dylib - compatibility_version 4 -current_version 4.1 -multiply_defined suppress fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe- lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe- secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../src/port -lresolv -o libpq.4.1.dylib /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) make[3]: *** [libpq.4.1.dylib] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 OSX v10.4.2 PostgreSQL v8.1.0 Any suggestions? Thanks Scott ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Kudos to the pgAdmin3 Team
After playing with 1.4 for a bit I must say you've all out done yourselves. This is a top quality release, from the refined UI to the added support for Slony-I. Keep up the good work. Apps like this move our community and the PostgreSQL project ahead, functional, clean, and professional. Regards, Gavin Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to use indexes for partial match at
Scott, >> I'm searching a way to use Postgres regular index for this. > > Easy, do what those other databases do. Setup your database to not use > a locale. > > initdb --locale=C > > and you're golden. thank you. My language has letters in õäöüþð with correstonding upper case letters ÕÄÖÜÞÐ I need to prevent entering of duplicate customer names into database. For this I created unique index on UPPER(customer_name) I need also search for customer name in case-insensitive way. For this I use ILIKE operator. Those two features are working in Postgres 8.1 when I use non-C locale. If I switch to C locale, they will not work. My current database, Microsoft Visual Foxpro implements this functionality. How to implement this functionality in Postgres if I switch to C locale ? Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best way to use indexes for partial match at beginning
>> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY); >> >> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1 >Do this instead: >CREATE TABLE foo ( bar CHAR(10) NOT NULL ); >CREATE UNIQUE INDEX foo_bar ON foo(bar char_pattern_ops); Martijn, Thank you. I have CHAR columns and need a primary key also. So I tried the code CREATE TABLE foo ( bar CHAR(10) NOT NULL ); CREATE UNIQUE INDEX foo_bar ON foo(bar bpchar_pattern_ops); ALTER TABLE foo ADD PRIMARY KEY (bar); I found that adding primary key creates another index. How to create primary key without duplicate index on bar column ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best way to use indexes for partial match at
On Wed, 2005-11-09 at 15:30, Andrus wrote: > Scott, > > >> I'm searching a way to use Postgres regular index for this. > > > > Easy, do what those other databases do. Setup your database to not use > > a locale. > > > > initdb --locale=C > > > > and you're golden. > > thank you. > > My language has letters in with correstonding upper case letters > > > I need to prevent entering of duplicate customer names into database. > For this I created unique index on UPPER(customer_name) > > I need also search for customer name in case-insensitive way. For this I use > ILIKE operator. > > Those two features are working in Postgres 8.1 when I use non-C locale. > If I switch to C locale, they will not work. > > My current database, Microsoft Visual Foxpro implements this functionality. > > How to implement this functionality in Postgres if I switch to C locale ? You can't. You have conflicting desires. PostgreSQL IS NOT FOXPRO. If you want to use foxpro, then do so. If you want to use PostgreSQL, then you'll either have to accept that you need to make certain accomodations to use it with a non-C locale, or accept a C locale and its limitations. You say that Foxpro implements this functionality, but are you sure that it gets things like collation correct? I.e. does it truly understand all the rules for what comes before something else in your language? Locales are a complex and difficult thing to get exactly right, and while, at first blush, Foxpro may seem to do the right thing, you may find it isn't doing EVERYTHING exactly right, and still having good performance. Then again, maybe it is. But PostgreSQL is limited to working either in a C locale and automatically using indexes for like 'abc%' queries but getting collation wrong, or working in the correct locale, not using indexes for like 'abc%', having to use the special class operator if you want likes to work, and getting the collation correct. If that doesn't work for you, your only real choice is to either use another database, or start hacking to make PostgreSQL the database you want it to be. It's not a simple problem, and there is no simple answer. And if you expect any database to not have things like this in it to deal with, you just haven't looked very hard at any of them. They've all got warts. And sometimes, one db is just not a good fit. Perhaps full text searching could help you out here? Not sure. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to use indexes for partial match at beginning
> SELECT * FROM foo WHERE bar::CHAR(1) = 'A'; > SELECT * FROM foo WHERE bar::CHAR(1) = 'B'; > SELECT * FROM foo WHERE bar::CHAR(2) = 'BC'; > SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC'; Dean, thank you. That would be EXCELLENT solution! Can you confirm that in this case Postgres 8.1 can use index created by CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY ); even on non-C locale ? Andrus. ---(end of broadcast)--- TIP 1: 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: [GENERAL] set-valued function difference in 8.1.0 vs 8.0.2
[EMAIL PROTECTED] writes: > I just wanted to make sure this change in behavior is > a feature and not a bug. Afraid so --- the plperl SRF support was rewritten in 8.1, and it probably behaves just like plpgsql now, which has also got this restriction. > Is there an easy way of rewriting this construct where the results of > a function can expand the result set that works nicely in 8.1? A kluge some people have used with plpgsql is to put a SQL-language wrapper function around the PL function, ie create function foo(...) returns ... as 'select * from pl_foo(...)' language sql; Should work for plperl too. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Best way to use indexes for partial match at beginning
On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote: > >> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY); > >> > >> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1 > > >Do this instead: > > >CREATE TABLE foo ( bar CHAR(10) NOT NULL ); > >CREATE UNIQUE INDEX foo_bar ON foo(bar char_pattern_ops); > > Martijn, > > Thank you. I have CHAR columns and need a primary key also. So I tried the > code > > CREATE TABLE foo ( bar CHAR(10) NOT NULL ); > CREATE UNIQUE INDEX foo_bar ON foo(bar bpchar_pattern_ops); > ALTER TABLE foo ADD PRIMARY KEY (bar); > > I found that adding primary key creates another index. > > How to create primary key without duplicate index on bar column ? > > Andrus. > > you can't. postgresql implements primary keys creating unique indexes and not null constraints on the pk columns. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: 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: [GENERAL] build errors on MacOSX
Scott Frankel <[EMAIL PROTECTED]> writes: > My build of postgresql-8.1.0 is failing on MacOSX. > /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) > file: -lSystem is not an object file (not allowed in a library) We've seen this before. IIRC, it means you're using outdated Xcode tools; you need to update to Xcode 2.0 or later (2.1 is current I believe). Try searching the list archives for that error message if you want more details. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to use indexes for partial match at
On Wed, 2005-11-09 at 16:23, Jaime Casanova wrote: > On 11/9/05, Andrus <[EMAIL PROTECTED]> wrote: > > >> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY); > > >> > > >> Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1 > > > > >Do this instead: > > > > >CREATE TABLE foo ( bar CHAR(10) NOT NULL ); > > >CREATE UNIQUE INDEX foo_bar ON foo(bar char_pattern_ops); > > > > Martijn, > > > > Thank you. I have CHAR columns and need a primary key also. So I tried the > > code > > > > CREATE TABLE foo ( bar CHAR(10) NOT NULL ); > > CREATE UNIQUE INDEX foo_bar ON foo(bar bpchar_pattern_ops); > > ALTER TABLE foo ADD PRIMARY KEY (bar); > > > > I found that adding primary key creates another index. > > > > How to create primary key without duplicate index on bar column ? > > > > Andrus. > > > > > > you can't. > postgresql implements primary keys creating unique indexes and not > null constraints on the pk columns. But, of course, you CAN delete that other index now that it's redundant. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Schemas shown in ODBC
Claus Scherschel wrote: I've just upgraded from Postgres 8.0.1 to 8.1. For testing purposes I'm using a MS Access Frontend connected by ODBC. This worked fine until I switched to the new ODBC-Driver (the Unicode-version). When I try to connect to some new tables, the ODBC-Driver shows tables from the information_schema only. same here Acc2K on Win2K Someone on the ODBC-list said Acc2K is not affected on WinXP. StarOffice8 on Win2K doesn't show this issue either. You could easily link your tables by a little VBA script within Acc2K even though linking by dialog won't be possible. Public Sub LinkTable() Const strSchema As String = "public" Const strTblInt As String = "tblTest" ' name of table within Access Const strTblExt As String = "t_test" ' name of table within Postgres Dim db As DAO.Database Dim tdf As DAO.TableDef Set db = CurrentDb() Set tdf = db.CreateTableDef(strTblInt) tdf.Connect = "ODBC;DSN=pg_dsn" tdf.SourceTableName = strTblExt db.TableDefs.Append tdf Set tdf = Nothing Set db = Nothing End Sub ---(end of broadcast)--- TIP 1: 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: [GENERAL] Best way to use indexes for partial match at beginning
Andrus wrote: > > SELECT * FROM foo WHERE bar::CHAR(1) = 'A'; > > SELECT * FROM foo WHERE bar::CHAR(1) = 'B'; > > SELECT * FROM foo WHERE bar::CHAR(2) = 'BC'; > > SELECT * FROM foo WHERE bar::CHAR(3) = 'ABC'; > Can you confirm that in this case Postgres 8.1 can use index created > by > CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY ); > even on non-C locale ? It will not use the index no matter what locale. You would in these cases need to create additional expression indexes on bar::char(1) etc. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question on Insert / Update
Will give that a try. thanks. was actually interested if the 2nd approach is common practice or if there are some reasons not to do it that way. Alex Sean Davis wrote: On 11/9/05 9:45 AM, "Alex" <[EMAIL PROTECTED]> wrote: Hi, have just a general question... I have a table of 10M records, unique key on 5 fields. I need to update/insert 200k records in one go. I could do a select to check for existence and then either insert or update. Or simply insert, check on the error code an update if required. The 2nd seems to be to logical choice, but will it actually be faster and moreover is that the right way to do it? Probably the fastest and most robust way to go about this if you have the records in the form of a tab-delimited file is to COPY or \copy (in psql) them into a separate loader table and then use SQL to manipulate the records (check for duplicates, etc) for final insertion into the table. Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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
[GENERAL] TRUNCATE Question
Hi, could anyone tell me if it is necessary to run a Vacuum after truncating a table or is that done automatically. Thanks Alex ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TRUNCATE Question
Alex <[EMAIL PROTECTED]> writes: > could anyone tell me if it is necessary to run a Vacuum after truncating > a table or is that done automatically. TRUNCATE does not require a vacuum; indeed there's nothing left *to* vacuum. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TRUNCATE Question
On Thu, Nov 10, 2005 at 01:16:24PM +1100, Alex wrote: > could anyone tell me if it is necessary to run a Vacuum after truncating > a table or is that done automatically. http://www.postgresql.org/docs/8.0/interactive/maintenance.html#VACUUM-FOR-SPACE-RECOVERY "If you have a table whose contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM. TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space." -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
