Re: [GENERAL] Postgres automatically inserts chr(13) whenever chr(10) is inserted
On Fri, Mar 03, 2006 at 08:47:00AM +0100, Dragan Matic wrote:
create table sample(column_sample varchar(500))
insert into sample(column_sample) values('this is first row of text' ||
chr(10) || 'this is second row of text')
Now, instead of just inserting chr(10), postgres inserts chr(13) +
chr(10). Is there a way to avoid this? Database is on a linux server
with SQL_ASCII encoding, clients are winXP communicating thru ODBC.
How did you determine what characters are being inserted? What's
the output of the following example?
INSERT INTO sample VALUES ('a' || chr(10) || 'b');
SELECT length(column_sample), decode(column_sample, 'escape') FROM sample;
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] query timeout
On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Physical column size
Hi,I've created a table like this : CREATE TABLE tmp_A (c char,i int4);And another one CREATE TABLE tmp_B (i int4, ii int4);I then inserted a bit more than 19 million rows in each table (exactly the same number of rows in each). The end result is that the physical size on disk used by table tmp_A is exactly the same as table tmp_B (as revealed by the pg_relation_size function) ! Given that a char field is supposed to be 1 byte in size and a int4 4 bytes, shouldn't the tmp_A use a smaller disk space ? Or is it that any value, whatever the type, requires at least 4 bytes to be stored ? Thanks,Paul
Re: [GENERAL] query timeout
Oh that will abort vacuum after that time as well? Can anyone confirm that this is the case? There shouldn't be ANY queries that take that long and if there are then can manually set the parameter when those requests happen. I would prefer to limit by default and allow longer queries only when I specify. But if it kills vacuum I will have to take a different approach. On Mar 3, 2006, at 2:59 AM, Ragnar wrote: On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to save and restore a template database ?
Michael Fuhr wrote: On Thu, Mar 02, 2006 at 05:17:12PM +0100, DANTE ALEXANDRA wrote: We have a question concerning backup / restore of database. Is it right if we say that for having a complete backup, we have do : 1. use pg_dumpall 2. do a backup of the modified template1 database with pg_dump ? Our question is how to save and restore a template database, as template1 ? pg_dumpall should dump the modified contents of template1. Is it not doing so? What version of PostgreSQL are you using? Thanks Michael, we use a 8.1.2 release and we first make tests to understand how pg_dump works .The first test was without a template1 modified. We redo this morning the test but with a modified template1, and yes it works fine Alexandra, Agnès ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query timeout
I asked the same question some time ago, and IIRC the answer was that the statement timeout only applies to interactive sessions. So autovacuum would not be affected, but a vacuum run through psql yes. You can also set it for a user (see alter user ... set ...), and use separate users for application access and maintenance work. Cheers, Csaba. On Fri, 2006-03-03 at 11:03, Rick Gigger wrote: Oh that will abort vacuum after that time as well? Can anyone confirm that this is the case? There shouldn't be ANY queries that take that long and if there are then can manually set the parameter when those requests happen. I would prefer to limit by default and allow longer queries only when I specify. But if it kills vacuum I will have to take a different approach. On Mar 3, 2006, at 2:59 AM, Ragnar wrote: On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Physical column size
Am Freitag, 3. März 2006 11:03 schrieb Paul Mackay: I've created a table like this : CREATE TABLE tmp_A ( c char, i int4 ); And another one CREATE TABLE tmp_B ( i int4, ii int4 ); The end result is that the physical size on disk used by table tmp_A is exactly the same as table tmp_B (as revealed by the pg_relation_size function) ! An int4 field is required to be aligned at a 4-byte boundary internally, so there are 3 bytes wasted between tmp_A.c and tmp_A.i. If you switch the order of the fields you should see space savings. (Note, however, that the per-row overhead is about 32 bytes, so you'll probably only save about 10% overall, rather than the 37.5% that one might expect.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Physical column size
On Fri, Mar 03, 2006 at 11:03:24AM +0100, Paul Mackay wrote: The end result is that the physical size on disk used by table tmp_A is exactly the same as table tmp_B (as revealed by the pg_relation_size function) ! Given that a char field is supposed to be 1 byte in size and a int4 4 bytes, shouldn't the tmp_A use a smaller disk space ? Or is it that any value, whatever the type, requires at least 4 bytes to be stored ? Alignment. An int4 value must start on a multiple of 4 offset, so you get three bytes of padding. If you put the int4, then the char it should work better. Although whole rows have alignment requirements too... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. signature.asc Description: Digital signature
Re: [GENERAL] Physical column size
On fös, 2006-03-03 at 11:03 +0100, Paul Mackay wrote: Hi, I've created a table like this : CREATE TABLE tmp_A ( c char, i int4 ); And another one CREATE TABLE tmp_B ( i int4, ii int4 ); I then inserted a bit more than 19 million rows in each table (exactly the same number of rows in each). The end result is that the physical size on disk used by table tmp_A is exactly the same as table tmp_B (as revealed by the pg_relation_size function) ! Given that a char field is supposed to be 1 byte in size and a int4 4 bytes, shouldn't the tmp_A use a smaller disk space ? Or is it that any value, whatever the type, requires at least 4 bytes to be stored ? the int4 needs to be aligned at 4 bytes boundaries, making wasted space after the char. this would probably be the same size: CREATE TABLE tmp_C ( c char, cc char, i int4 ); and this would be smaller: CREATE TABLE tmp_D ( c char, cc char, ccc char, ); P.S.: I did not actually check to see if the char type needs to be aligned, by I assumed not. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Question about the contrib rpm ?
Hello Tom, hello List, Sorry if we haven't been clear in our first mail. We don't really understand your answer. So, we try to clarify our general question and give more details : When we go on the web site to download PostgreSQL 8.1.2, we find not only the serveur rpm but also some others rpms. and we don't kow which of them we have to install together with our rpm make from the 8.1.2 targz. We are making our rpm on Novascale Ia64 We have used the postgresql-8.1.2.tar.gz file downloaded from the PostGreSQL web site. From that file, we have re-compiled PostGreSQL for IA64 on Red Hat Enterprise Linux 4 AS, with the icc Intel compiler. We would like to 'deliver' a more complete set as possible. and we don't know if we have to package some others packages.. For the langage python,perl,tcl ...we know that if we need them we have to use the --with option when we compile. It seems also to us that it is not necessary to have the lib rpm as the necessary librairies are include in the rpm when wecompile and package it. but ..we dont' know what doing with the *contrib *rpm available on the net, should we have to deliver it with our rpm. Why this question ? When we have extract files from the archive file, we have obtained these directories : [/BUILD/postgresql-8.1.2]$ ls -ltr total 1528 -rw-r--r-- 1 postdev pgsql445 Apr 23 2004 aclocal.m4 -rw-r--r-- 1 postdev pgsql 1375 Oct 1 2004 README -rw-r--r-- 1 postdev pgsql 1412 Oct 6 2004 Makefile -rw-r--r-- 1 postdev pgsql 1192 Dec 31 2004 COPYRIGHT -rw-r--r-- 1 postdev pgsql 3435 May 1 2005 GNUmakefile.in -rwxr-xr-x 1 postdev pgsql 689752 Jan 5 05:02 configure -rw-r--r-- 1 postdev pgsql 43596 Jan 5 05:02 configure.in -rw-r--r-- 1 postdev pgsql 387774 Jan 6 05:09 HISTORY -rw-r--r-- 1 postdev pgsql 44484 Jan 6 05:09 INSTALL drwxr-xr-x 2 postdev pgsql 4096 Jan 6 05:09 config drwxr-xr-x 35 postdev pgsql 4096 Jan 6 05:09 *contrib* -rw-r--r-- 1 postdev pgsql 3435 Feb 16 12:22 GNUmakefile -rwxr-xr-x 1 postdev pgsql 56658 Feb 16 12:22 config.status drwxr-xr-x 15 postdev pgsql 4096 Feb 16 12:22 src drwxr-xr-x 7 postdev pgsql 4096 Feb 16 12:22 doc -rw-r--r-- 1 postdev pgsql 278305 Feb 16 12:22 config.log Under the contrib repertory, we have among others things *start-scripts* directory which contains the linux file which allows to launch automatically PostGreSQL each time the machine reboots. [ contrib]# ls adddepend dblink intarray mSQL-interface pgstattupletablefunc btree_gist dbmirror isbn_issn oid2name pg_trgmtips chkpassearthdistance lo oracle README tsearch2 contrib-global.mk fulltextindex ltree pgbench seguserlock cube fuzzystrmatch macpg_buffercache spivacuumlo dbase intagg Makefile pgcrypto start-scripts xml2 And when we install our rpm, we don't have a contrib directory such as this obtains after having extracted the files from the tar.gz archive. Our question is how to add in the rpm that we have generated a contrib directory, in order to have scripts like linux ? Or should we used the contrib rpm available in the web site Or we have to regenerate the contrib rpm from the source rpm? Or should we install the contrib rpm available in the web site, even if it has not been compiled for IA64 ? Or Should we recompile this rpm with gcc and it will be compatible with our rpm compiled with icc? We found in the archive documentation information about start-script, we don't find anything else about init script, what do you mean with 'init script' , is-it the pg_ctl command ? and is there a documentation abou all 'things' we see under the contrib directory (from the targz) Thank you having read us until the end and thanks again for your help regards AA Tom Lane wrote: DANTE ALEXANDRA [EMAIL PROTECTED] writes: We have a question about the contrib rpm. To have some tools like the start_scripts, should we : I don't believe the RPM distribution includes contrib/start-scripts (mainly because that contrib directory has no Makefile that would install the scripts). I cannot see why you'd want them, since the postgresql-server RPM already has an init script that is considerably more appropriate for RPM-based distros than the one that's in contrib. 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 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] tsearch2 match substrings
Hi, Is it possible using tsearch2 to match a substring of an element of a ts_vec column? For example: I have a table with a ts_vec, and I have a field with text, and if there is a value in the field I would like to match the beginning of the words - and the ts_vec has 'like':3 'match':5 'begin':7 ...etc then it would be cool to create a query, which give me a headline for begin if I just use the beg (or beg* ) search expression. Is there a way to solve this? Thanks in advance. Abbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: column datpath does not exist
Yes. Your server must be PG 8.0 or 8.1. Using the -i switch to override pg_dump's version check is hardly ever a good idea --- instead, find a newer version of pg_dump. Sorry, my mistake. Actually I am using PostgreSQL 8.0.3, but pg_dump is 7.3.10-RH ! So, is there a way to get a newer version of pg_dump? I guess that's what could help me out. Please help. Sascha ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux cluster application
Andrew Watkins [EMAIL PROTECTED] writes: Thanks. I suppose my question is less about the mechanisms for actually starting the servers and more about where to install the servers, where they should be running, etc. For example, if I'm using a shared file system across each node in the cluster and postgres has been installed in, say, /home/user/pgres, and initdb has initialized the database on, say, /home/user/pgres/data, then it would seem like there would end up being conflicts in file names when trying to launch a local server on each node. On the other hand, if there is disk space local to each node, then running the servers there would not allow for the assessing of the impact on a parallel file system. You will definitely have to run initdb, and start Postgres, with a unique data directory for each machine (maybe named after the host?) -- having more than one server process trying to use a single directory will break everything. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] extended index info
Michael Fuhr [EMAIL PROTECTED] writes: On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote: If I look at an index: It doesn't show me which fields it actually applies to, only the table. \d news_pkey Also, \d on the index's parent table will show you all the index definitions. This is more useful than the \d index display in some cases, particularly non-default opclasses and index expressions. For example: regression=# create index fooi on tenk1((unique1+unique2)); CREATE INDEX regression=# \d fooi Index public.fooi Column | Type -+- pg_expression_1 | integer btree, for table public.tenk1 regression=# \d tenk1 ... Indexes: fooi btree ((unique1 + unique2)) ... I'm not really sure why we don't account for these cases in \d index, unless that it's hard to see where to fit the info into a tabular layout. 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] Physical column size
Peter Eisentraut [EMAIL PROTECTED] writes: An int4 field is required to be aligned at a 4-byte boundary internally, so there are 3 bytes wasted between tmp_A.c and tmp_A.i. If you switch the order of the fields you should see space savings. Probably not, because the row-as-a-whole has alignment requirements too. In this example you'll just move the pad bytes from one place to another. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgres automatically inserts chr(13) whenever chr(10) is inserted
Dragan Matic [EMAIL PROTECTED] writes:
create table sample(column_sample varchar(500))
insert into sample(column_sample) values('this is first row of text' ||
chr(10) || 'this is second row of text')
Now, instead of just inserting chr(10), postgres inserts chr(13) +
chr(10).
Postgres most certainly does not do that, as even a moment's
experimentation (eg, with length()) will prove to you. Take a closer
look at your client-side software to find out where the newline
conversion is happening.
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] SELinux strangeness with 8.1.2 and 8.1.3
Hi Tom, Hmm. That seems like a SELinux policy bug. It doesn't happen for me: the pid file is created with the same context the other files have. I agree! I have the latest FC4 policy update. So I downloaded the sources as the new one didn't solve the issue. The policy source has no mention on the pid file, but it seems like it should be created with the settings of the directory, which is set correctly. I'm not an expert in SELinux, so I didn't want to mess with the policy, though I think the pid file could be added to the policy specifically to solve this issue. Also, I did run restorecon on the directory (that was the first thing I tried), but it didn't help. Probably because the pid file isn't there when postgres isn't running. Today I will have the results from my second machine update, as it just finished installing all the FC4 updates through yum. I'll let you know how it goes. Bye, Guy. -rw--- postgres postgres root:object_r:postgresql_db_tpostmaster.pid Are you sure that your SELinux policy is up-to-date? Maybe you need to do a restorecon on the postgres binaries and/or /var/lib/pgsql/data. Some more info about the system: * FC4 fully updated * Postgres 8.1.3 built from the PGDG SRPMs * Dual Opteron I tried it myself on a freshly-updated FC4 x86_64 system, using the current FC5 SRPMs, and couldn't see a problem. Red Hat's SRPMs are not exactly like the PGDG ones, but the only difference I can find that looks at all relevant to SELinux is this one in the init script: 132c134 [ -x /usr/bin/chcon ] /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t $PGLOG --- [ -x /usr/bin/chcon ] /usr/bin/chcon -t postgresql_log_t $PGLOG and that's not about the pid file. regards, tom lane -- Bye, Guy Family management on rails: http://www.famundo.com - coming soon! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tsearch2 match substrings
words - and the ts_vec has 'like':3 'match':5 'begin':7 ...etc then it would be cool to create a query, which give me a headline for begin if I just use the beg (or beg* ) search expression. Potentially yes, but without index support and it's needed to write your operator/function. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELinux strangeness with 8.1.2 and 8.1.3
I just finished installing the PGDG rpms on my second server. This one is a single CPU Opteron with 2 SATA based RAID5 arrays. (Just to clear things up, I know RAID5 is bad for postgres, but this is a storage server that has postgres only as a backup for the main machine.) The problem diesn'[t happen on this second machine. Which leaves me with one big difference between the two machines. The main DB server (the one with the problem) has the RAID10 array mounted exclusively for it's access, and under it the pg_xlog directory is mounted on another exclusive partition on the other array. Here are the mount details: /dev/sdb1 on /var/lib/pgsql type xfs (rw,noatime,nodiratime,logbufs=8) /dev/sda3 on /var/lib/pgsql/data/pg_xlog type ext2 (rw,noatime,nodiratime) Any idea if this might be causing the problem? I don't see how it might do it, but as I said I'm not an SELinux expert. Bye, Guy. On 3/3/06, Just Someone [EMAIL PROTECTED] wrote: Hi Tom, Hmm. That seems like a SELinux policy bug. It doesn't happen for me: the pid file is created with the same context the other files have. I agree! I have the latest FC4 policy update. So I downloaded the sources as the new one didn't solve the issue. The policy source has no mention on the pid file, but it seems like it should be created with the settings of the directory, which is set correctly. I'm not an expert in SELinux, so I didn't want to mess with the policy, though I think the pid file could be added to the policy specifically to solve this issue. Also, I did run restorecon on the directory (that was the first thing I tried), but it didn't help. Probably because the pid file isn't there when postgres isn't running. Today I will have the results from my second machine update, as it just finished installing all the FC4 updates through yum. I'll let you know how it goes. Bye, Guy. -rw--- postgres postgres root:object_r:postgresql_db_t postmaster.pid Are you sure that your SELinux policy is up-to-date? Maybe you need to do a restorecon on the postgres binaries and/or /var/lib/pgsql/data. Some more info about the system: * FC4 fully updated * Postgres 8.1.3 built from the PGDG SRPMs * Dual Opteron I tried it myself on a freshly-updated FC4 x86_64 system, using the current FC5 SRPMs, and couldn't see a problem. Red Hat's SRPMs are not exactly like the PGDG ones, but the only difference I can find that looks at all relevant to SELinux is this one in the init script: 132c134 [ -x /usr/bin/chcon ] /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t $PGLOG --- [ -x /usr/bin/chcon ] /usr/bin/chcon -t postgresql_log_t $PGLOG and that's not about the pid file. regards, tom lane -- Bye, Guy Family management on rails: http://www.famundo.com - coming soon! -- Bye, Guy Family management on rails: http://www.famundo.com - coming soon! ---(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] ERROR: column datpath does not exist
What's that datpath about? Is it a version problem? postgres (PostgreSQL) 7.3.10-RH Just set up a new postgres on another server. Restoring my dump and then trying pg_dump again. Here's the result: pg_dump mpv pg_dump: server version: PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux); pg_dump version: 7.3.9 pg_dump: aborting because of version mismatch (Use the -i option to proceed anyway.) pg_dump mpv -i pg_dump: server version: PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux); pg_dump version: 7.3.9 pg_dump: proceeding despite version mismatch pg_dump: SQL command failed pg_dump: Error message from server: ERROR: column datpath does not exist pg_dump: The command was: select (select usename from pg_user where usesysid = datdba) as dba, encoding, datpath from pg_database where datname = 'mpv' I cannot understand why this datpath-thing happens again... :o( Regards, Sascha ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SELECT Question
Hi, i want to calculate the price difference, change% of 2 price records. Is there an easy way to do that within one query, rather than writing a function? Example: company, price_date, price, change, change_perc compA, 20060203,100,,, compA, 20060202,100,,, compA, 20060201,100,,, for one, i would like to calculate for one date the changes (price, and percentage) to the previous day and update the record. i also want to be able to run that through the entire history to re-calculate the changes i.e. not only update one record but update all of these. Currently I am doing it with a function but I wonder if there is an easier and faster way. Any suggestion or help would be appreciated. Thanks in advance Alex ---(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] Problem with function that returns a cursor
I'm new to pgSQL and I'm having some trouble with a
function. I keep getting the following error:
org.postgresql.util.PSQLException: ERROR: cursor
unnamed portal 1 does not exist
This is the function:
CREATE OR REPLACE FUNCTION ret_user(pusername
varchar)
RETURNS refcursor AS
$BODY$
DECLARE
ccursor refcursor;
BEGIN
open ccursor for
select username,
password,
idrole
from user
where username = quote_literal($1);
RETURN ccursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
This is the JDBC code:
CallableStatement statement = null;
ResultSet rs = null;
Connection connection =
ConnectionPool.getConnection(saJNDI);
try{
statement = connection.prepareCall({? =
call ret_user(?));
statement.registerOutParameter(1,
Types.OTHER);
statement.setObject(2, munoze);
statement.execute();
rs = (ResultSet)statement.getObject(1);
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
rs.close();
statement.close();
} catch(SQLException e){
}
I hope you can help me
__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: column datpath does not exist
On Fri, 2006-03-03 at 10:38, Sascha Nepper wrote: What's that datpath about? Is it a version problem? postgres (PostgreSQL) 7.3.10-RH Just set up a new postgres on another server. Restoring my dump and then trying pg_dump again. Here's the result: pg_dump mpv pg_dump: server version: PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux); pg_dump version: 7.3.9 pg_dump: aborting because of version mismatch (Use the -i option to proceed anyway.) pg_dump mpv -i pg_dump: server version: PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux); pg_dump version: 7.3.9 pg_dump: proceeding despite version mismatch pg_dump: SQL command failed pg_dump: Error message from server: ERROR: column datpath does not exist pg_dump: The command was: select (select usename from pg_user where usesysid = datdba) as dba, encoding, datpath from pg_database where datname = 'mpv' I cannot understand why this datpath-thing happens again... :o( It's simple. Under 8.1.3 something was added to the database that the new 8.1.3 pg_dump knows about that the 7.3.9 pg_dump couldn't know about, because it was written a couple years before that had been added to postgresql, in 8.x (whatever version). It's quite reasonable to use a newer (i.e. higher version number) version of pg_dump on an older database, because it's fairly easy to incorporate backwards compatibility into pg_dump. But without time travel (not temporal database sets, but real, honest, get in a box and travel backwards in time) it's pretty hard for an older version of pg_dump to know how to handle future versions of postgresql, cause they didn't exist when that version of pg_dump was written. So, the version of pg_dump should be equal to or greater than the version of the postgresql database you are dumping, got it? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT Question
On Sat, Mar 04, 2006 at 03:35:02 +1100, Alex [EMAIL PROTECTED] wrote: Hi, i want to calculate the price difference, change% of 2 price records. Is there an easy way to do that within one query, rather than writing a function? You can use a self join to do this. It won't be spectaculatly fast though. The idea is too join each record to the record with the largest date less than the date of the current record. Note that on record won't have a match. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with function that returns a cursor
On Fri, Mar 03, 2006 at 11:06:20AM -0600, Eduardo Muñoz wrote: I'm new to pgSQL and I'm having some trouble with a function. I keep getting the following error: org.postgresql.util.PSQLException: ERROR: cursor unnamed portal 1 does not exist See my reply to your previous message about this: http://archives.postgresql.org/pgsql-general/2006-03/msg00094.php -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] record OID to table
How can I possible to find out what table a record belong to from record's oid? Thanks Yudie
Re: [GENERAL] record OID to table
On Fri, Mar 03, 2006 at 11:31:40AM -0600, Yudie Pg wrote: How can I possible to find out what table a record belong to from record's oid? You can't. An OID doesn't identify the record since it's not guarenteed to be unique (usually). In a query you can use tableoid but that's about it. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org 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. signature.asc Description: Digital signature
Re: [GENERAL] record OID to table
On Fri, Mar 03, 2006 at 11:31:40AM -0600, Yudie Pg wrote: How can I possible to find out what table a record belong to from record's oid? You can't, short of querying every table that has an oid column. However, you could determine the table from the record's tableoid column. Where are you getting the oid? Could you tell us more about what you're trying to do? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tsearch2 match substrings
Hello Teodor, Friday, March 3, 2006, 5:09:45 PM, you wrote: words - and the ts_vec has 'like':3 'match':5 'begin':7 ...etc then it would be cool to create a query, which give me a headline for begin if I just use the beg (or beg* ) search expression. Potentially yes, but without index support and it's needed to write your operator/function. Could You write some sample how do You mean? Thanks in advance. -- Abbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] User tables
Hello All,Suppose there 3 users red, green, blue. How can the user green know what tables he has created?!From psql command line \dt lists every table in the DB!!!Thanks in advance.Hrishi
Re: [GENERAL] User tables
Hrishikesh Deshmukh wrote: Hello All, Suppose there 3 users red, green, blue. How can the user green know what tables he has created?! From psql command line \dt lists every table in the DB!!! Thanks in advance. Hrishi If you mean that the owner of the table(s) is the user green, then try select * from pg_tables where tableowner='green'; ---(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] query timeout
I assume that running the vacuumdb command is the same as running it through psql? On Mar 3, 2006, at 3:14 AM, Csaba Nagy wrote: I asked the same question some time ago, and IIRC the answer was that the statement timeout only applies to interactive sessions. So autovacuum would not be affected, but a vacuum run through psql yes. You can also set it for a user (see alter user ... set ...), and use separate users for application access and maintenance work. Cheers, Csaba. On Fri, 2006-03-03 at 11:03, Rick Gigger wrote: Oh that will abort vacuum after that time as well? Can anyone confirm that this is the case? There shouldn't be ANY queries that take that long and if there are then can manually set the parameter when those requests happen. I would prefer to limit by default and allow longer queries only when I specify. But if it kills vacuum I will have to take a different approach. On Mar 3, 2006, at 2:59 AM, Ragnar wrote: On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Updates to my PostgreSQL Wiki
I've got some new content on my wiki for PostgreSQL. http://www.bluwiki.org/go/PostgreSQL_Tips_From_The_Sunburned_Surveyor You will find an article on making connections to the PostgreSQL server, and an article on creating automated backups of PostgreSQL databases using a bash shell script. These are two areas I didn't find much info on as a newbie. I managed to work through them with some help and wanted to post my solutions for other PostgreSQL/Linux newbies. If more experienced users read over the articles and find mistakes or something that could be imporved, please let me know. I hope others in the community will benefit from the articles. The Sunburned Surveyor P.S. - I still have some sections to complete on the article about automatic backups. I was also wondering, what is the -c switch used for? What type of custom format would you export in a postgreSQL backup? ---(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] Question about the contrib rpm ?
On 3/3/06, Agnes Bocchino [EMAIL PROTECTED] wrote: Hello Tom, hello List, Sorry if we haven't been clear in our first mail. We don't really understand your answer. So, we try to clarify our general question and give more details : When we go on the web site to download PostgreSQL 8.1.2, we find not only the serveur rpm but also some others rpms. and we don't kow which of them we have to install together with our rpm make from the 8.1.2 targz. We are making our rpm on Novascale Ia64 We have used the postgresql-8.1.2.tar.gz file downloaded from the PostGreSQL web site. From that file, we have re-compiled PostGreSQL for IA64 on Red Hat Enterprise Linux 4 AS, with the icc Intel compiler. We would like to 'deliver' a more complete set as possible. and we don't know if we have to package some others packages.. For the langage python,perl,tcl ...we know that if we need them we have to use the --with option when we compile. It seems also to us that it is not necessary to have the lib rpm as the necessary librairies are include in the rpm when wecompile and package it. but ..we dont' know what doing with the *contrib *rpm available on the net, should we have to deliver it with our rpm. Why this question ? When we have extract files from the archive file, we have obtained these directories : [/BUILD/postgresql-8.1.2]$ ls -ltr total 1528 -rw-r--r-- 1 postdev pgsql445 Apr 23 2004 aclocal.m4 -rw-r--r-- 1 postdev pgsql 1375 Oct 1 2004 README -rw-r--r-- 1 postdev pgsql 1412 Oct 6 2004 Makefile -rw-r--r-- 1 postdev pgsql 1192 Dec 31 2004 COPYRIGHT -rw-r--r-- 1 postdev pgsql 3435 May 1 2005 GNUmakefile.in -rwxr-xr-x 1 postdev pgsql 689752 Jan 5 05:02 configure -rw-r--r-- 1 postdev pgsql 43596 Jan 5 05:02 configure.in -rw-r--r-- 1 postdev pgsql 387774 Jan 6 05:09 HISTORY -rw-r--r-- 1 postdev pgsql 44484 Jan 6 05:09 INSTALL drwxr-xr-x 2 postdev pgsql 4096 Jan 6 05:09 config drwxr-xr-x 35 postdev pgsql 4096 Jan 6 05:09 *contrib* -rw-r--r-- 1 postdev pgsql 3435 Feb 16 12:22 GNUmakefile -rwxr-xr-x 1 postdev pgsql 56658 Feb 16 12:22 config.status drwxr-xr-x 15 postdev pgsql 4096 Feb 16 12:22 src drwxr-xr-x 7 postdev pgsql 4096 Feb 16 12:22 doc -rw-r--r-- 1 postdev pgsql 278305 Feb 16 12:22 config.log Under the contrib repertory, we have among others things *start-scripts* directory which contains the linux file which allows to launch automatically PostGreSQL each time the machine reboots. [ contrib]# ls adddepend dblink intarray mSQL-interface pgstattupletablefunc btree_gist dbmirror isbn_issn oid2name pg_trgmtips chkpassearthdistance lo oracle README tsearch2 contrib-global.mk fulltextindex ltree pgbench seguserlock cube fuzzystrmatch macpg_buffercache spivacuumlo dbase intagg Makefile pgcrypto start-scripts xml2 And when we install our rpm, we don't have a contrib directory such as this obtains after having extracted the files from the tar.gz archive. Our question is how to add in the rpm that we have generated a contrib directory, in order to have scripts like linux ? If you're building your own rpm you'll need to do it yourself - that's getting beyond what this list can help you with. Or should we used the contrib rpm available in the web site No - because it's not built for your system or with your compiler, it may work but you may also run into strange bugs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] installing xml2 for pg 8.1 on winxp
I hope I am reporting this in the right forum, if not please tell me so and next time I'll be better. There might be a problem with a missing extension in the pre-compiled windows version of Postgres 8.1 It looks as though it's the 'MemoryContextSwitchTo' library which is present in 8.0 but missing from 8.1. I was trying to install the xml2 library to Postgres as I found it on http://www.throwingbeans.org/postgresql_and_xml_updated.html but I had no luck. So I contacted Tom Dyson the author of the package and he informed me that there were others having a similar problem with the 8.1 compile while the library worked fine in the 8.0.3 version and he suggested that based on e-mails he got the problem might be the one I mentioned above. I just offered to report this problem of the build, unfortunately I can't help much more as this is far too high level for me. The actual error I get is: When I run CREATE OR REPLACE FUNCTION xml_valid(text) RETURNS bool AS 'libpgxml' LANGUAGE 'c' WITH (isStrict); I got the error message ERROR: could not load library C:/Program Files/PostgreSQL/8.1/lib/libpgxml.dll: The specified procedure could not be found. Thanks for the help. Balázs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to determine the table a query or a views columns come from?
Is there way to determine the table a query or a view's columns come from? I am looking for something like that in postgresql. If it doesn't exist is there some generalized SQL that can parse the query or view's definition and retrieve the list. something like column name | actual column name | source table | actual source table column name is either the original name or an alias for it, and so is source table. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to determine the table a query or a views columns come from?
Frank Church [EMAIL PROTECTED] writes: Is there way to determine the table a query or a view's columns come from? Yeah, there's some support for that in the protocol. libpq exposes it as PQftable() and PQftablecol(). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Updates to my PostgreSQL Wiki
On Mar 4, 2006, at 7:04 , Redefined Horizons wrote: P.S. - I still have some sections to complete on the article about automatic backups. I was also wondering, what is the -c switch used for? What type of custom format would you export in a postgreSQL backup? Are you referring to the -Fc flag for pg_dump? Something that I was recently made aware of was this[1]: Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default. You can use the --list and --use-list flags for pg_restore[2] to take advantage of the custom format dump file. Hope this helps. Michael Glaesemann grzm myrealbox com [1](http://www.postgresql.org/docs/current/interactive/app-pgdump.html) [2](http://www.postgresql.org/docs/current/interactive/app- pgrestore.html) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Accessing composite type columns in indexes
Michael Fuhr's example of using composite types for date intervals/ ranges/periods prompted me to explore this a little further. While doing so, it appears that one can't directly access the columns of a composite type when creating an index, i.e., neither UNIQUE (foo.bar) nor UNIQUE ((foo).bar) work. I was able to create indexes including composite columns by creating functions that returned values from a composite type parameter. Is this expected? Here are the details: begin; BEGIN -- closed-open date interval [from_date, to_date) create type date_co_interval as ( from_date date , to_date date ); CREATE TYPE create function co_begin(date_co_interval) returns date strict immutable security definer language plpgsql as ' declare i alias for $1; begin return i.from_date; end; '; CREATE FUNCTION -- convenience function create function prior(date) returns date strict immutable security definer language plpgsql as ' declare p alias for $1; begin return p - 1; end; '; CREATE FUNCTION create function co_end(date_co_interval) returns date strict immutable security definer language plpgsql as ' declare i alias for $1; begin return prior(i.to_date); end; '; CREATE FUNCTION savepoint composite_dot; SAVEPOINT create table employment_history ( company text not null , during date_co_interval not null , unique (company, during.from_date, during.to_date) ); ERROR: syntax error at or near . at character 129 LINE 5: , unique (company, during.from_date, during.to_date) ^ rollback to savepoint composite_dot; ROLLBACK savepoint composite_parens; SAVEPOINT create table employment_history ( company text not null , during date_co_interval , unique (company, (during).from_date, (during).to_date) ); ERROR: syntax error at or near ( at character 114 LINE 5: , unique (company, (during).from_date, (during).to_date) ^ rollback to savepoint composite_parens; ROLLBACK savepoint function_on_composite; SAVEPOINT create table employment_history ( company text not null , during date_co_interval not null , unique (company, co_begin(during), co_end(during)) ); ERROR: syntax error at or near ( at character 131 LINE 5: , unique (company, co_begin(during), co_end(during)) ^ rollback to savepoint function_on_composite; ROLLBACK savepoint parens_function_on_composite; SAVEPOINT create table employment_history ( company text not null , during date_co_interval not null , unique (company, (co_begin(during)), (co_end(during))) ); ERROR: syntax error at or near ( at character 123 LINE 5: , unique (company, (co_begin(during)), (co_end(during))) ^ rollback to savepoint parens_function_on_composite; ROLLBACK create table employment_history ( company text not null , during date_co_interval not null ); CREATE TABLE savepoint composite_idx; SAVEPOINT create unique index employment_history_pkey_idx on employment_history (company, during.from_date, during.to_date); ERROR: syntax error at or near , at character 98 LINE 2: on employment_history (company, during.from_date, during.to_... ^ rollback to savepoint composite_idx; ROLLBACK savepoint composite_parens_idx; SAVEPOINT create unique index employment_history_pkey_idx on employment_history (company, (during).from_date, (during).to_date); ERROR: syntax error at or near . at character 89 LINE 2: on employment_history (company, (during).from_date, (during)... ^ rollback to savepoint composite_parens_idx; ROLLBACK create unique index employment_history_pkey_idx on employment_history (company, co_begin(during), co_end(during)); CREATE INDEX rollback; ROLLBACK select version(); version -- PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5247) (1 row) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Insert fails when it shouldn't
What encoding did you initdb with? On Thu, Mar 02, 2006 at 04:59:39PM +0200, Anakreon Mendis wrote: The table: CREATE TABLE DILOSIS ( DL_AEM INT NOT NULL, DL_CODE VARCHAR(6) NOT NULL, DL_YEAR INT NOT NULL, DL_GRADE FLOAT(3) NOT NULL, DL_LESTYPE INT NOT NULL, DL_SEMESTER INT NOT NULL, DL_DM INT NOT NULL, DL_COEF FLOAT(3), --Syntelestis ptyxiou DL_CAT_NORM BOOLEAN NOT NULL, --TRUE NORMAL, FALSE APALLAGI DL_TEI INT NOT NULL, DL_THERINO BOOLEAN NOT NULL ); ALTER TABLE DILOSIS ADD CONSTRAINT pk_dilosis PRIMARY KEY (DL_AEM, DL_CODE, DL_YEAR, DL_TEI, DL_SEMESTER); The table is empty and statements stored in a file are executed with psql. = Statements === \encoding iso_8859_7 delete from dilosis; INSERT INTO DILOSIS VALUES(1, '??0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true); INSERT INTO DILOSIS VALUES(1, '??0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true); The second insert fails with an error message: psql:a.sql:4: ERROR: duplicate key violates unique constraint pk_dilosis The second insert is identical to the first one except the DL_CODE value. 1:??0100 (Gamma Epsilon ..) 2:??0100 (Heta Ypsilon ..) I think what is hapening is that the two first letters are ignored and the two values are found identical since the postfix is 0100 for bouth of them. What can be done so the second instert does not fail? Anakreon -- Three words describe our society:homo homini lupus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- 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 6: explain analyze is your friend
Re: [GENERAL] Solaris 10 ZFS Postgresql request for comments
On Thu, Mar 02, 2006 at 12:30:32PM -0500, Reid Thompson wrote: Hi all, I'm querying for feedback/comments. Wondering what the list thinks of the following. Assume this is to provide a production database for a small company or a department. Production hours 5am-9pm for the most part so night-time downtime if/when necessary would not be a problem. Platform: SUN ultra 20 or intel/amd based PC Hard drive1 = OS Solaris 10(+) Hard drives2 3 = ZFS mirrored pool with PostgreSQL intalled Hard drives4-N = ZFS raided PGDATA Would this be considered viable? Has anyone implemented anything similar? Any obvious pitfalls that anyone is aware of? I'd recommend drive 1 2 be mirrored witheverything but the table data, which would go on a raid 10 of the rest of the drives via a tablespace. That way you won't lose the box if drive 1 fails. You also probably don't want raid5, if you were thinking about that... -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [Fwd: Schema Question]
On Thu, Mar 02, 2006 at 03:45:01PM -0500, Oisin Glynn wrote:
I have a seperate schema with a function and table. The function does a
select on the table.
The table and function exist in public schema and betteridea schema. I
have a user betteridea who owns the betteridea schema.
snip
If I connect as postgres user and do select * from
betteridea.zfunc_get_employee('1234');
or
select * from zfunc_get_employee('1234');
with no schema in front of it.
It is returning the data from the public.employee_list in both cases!
Take a look at searh_path in the manual.
--
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 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] record OID to table
[Please copy the mailing list on replies.] On Fri, Mar 03, 2006 at 01:42:15PM -0600, Yudie Pg wrote: I was trying to make a indexing table that use fulltext indexing that could store all string values from the other tables in the database. I hope that can use record oid as the key and can be joined with the actual table. The oid alone doesn't identify a row's table, and even the pair (tableoid, oid) isn't guaranteed to be unique unless the table has a primary key or unique index on oid. The documentation discourages the use of oids as primary keys, and they're disabled by default as of 8.1. You could identify rows and their tables with (tableoid, primary_key), where primary_key is whatever primary key the tables use (including oid if it's declared as the primary key). You wouldn't be able to join the lookup table against a row's source table with ordinary SQL, but you could write a function that uses dynamic SQL to generate and execute an appropriate query. The reason I want to do that way instead querying directly to the table is because most of the tables may contain only reference id not the actual string value. Joining the table is not my option because it will lead to complicated and heavy query cost. Could you explain what you're trying to do without reference to how you're trying to do it? It sounds like the goal is to take an arbitrary string and find out what rows in what tables contain that string. Is that right? If so them I'm not sure how to best solve that problem; maybe somebody else will have some ideas. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Accessing composite type columns in indexes
Michael Glaesemann [EMAIL PROTECTED] writes: ... it appears that one can't directly access the columns of a composite type when creating an index, i.e., neither UNIQUE (foo.bar) nor UNIQUE ((foo).bar) work. You need both, ie something like create table foo (bar date_co_interval); create unique index fooi on foo (((bar).from_date)); The outer set of parens is required for any index expression. Basically that's to fix a grammar conflict against the possible presence of an index opclass, that is given create index fooi on foo (x ! y) is that an infix operator expression x ! y, or a postfix operator expression x ! followed by an opclass name? The inner set of parens is because a.b is always interpreted as a table and column name. To refer to a column, and then qualify it with a composite-type field, we require you to write (b).c or (a.b).c. It'd be legal to write the same index as create unique index fooi on foo (((foo.bar).from_date)); Make sense now? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Accessing composite type columns in indexes
On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: create unique index employment_history_pkey_idx on employment_history (company, (during).from_date, (during).to_date); ERROR: syntax error at or near . at character 89 LINE 2: on employment_history (company, (during).from_date, (during)... ^ This works: create unique index employment_history_pkey_idx on employment_history (company, ((during).from_date), ((during).to_date)); -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Accessing composite type columns in indexes
On Mar 4, 2006, at 13:34 , Michael Fuhr wrote: On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: create unique index employment_history_pkey_idx on employment_history (company, (during).from_date, (during).to_date); ERROR: syntax error at or near . at character 89 LINE 2: on employment_history (company, (during).from_date, (during)... ^ This works: create unique index employment_history_pkey_idx on employment_history (company, ((during).from_date), ((during).to_date)); Thanks, Tom and Michael. I thought I had been aggressive enough in applying parentheses. On Mar 4, 2006, at 13:31 , Tom Lane wrote: Make sense now? Yep! Michael Glaesemann grzm myrealbox com ---(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] Accessing composite type columns in indexes
On Mar 4, 2006, at 13:44 , Michael Glaesemann wrote: On Mar 4, 2006, at 13:31 , Tom Lane wrote: Make sense now? Yep! Except, why doesn't it work in the CREATE TABLE statement? One needs to add the UNIQUE index as a separate command. For example, -- doesn't work create table foo ( foo date_co_interval , unique (((foo).from_date), ((foo).to_date)) ); -- fails (as expected) create table foo ( foo date_co_interval , unique (((foo.foo).from_date), ((foo.foo).to_date)) ); -- works create table foo (foo date_co_interval); create unique index foo_idx on foo (((foo).from_date), ((foo).to_date)); Is this also excluded because of some parser ambiguity? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Accessing composite type columns in indexes
Michael Glaesemann [EMAIL PROTECTED] writes: Except, why doesn't it work in the CREATE TABLE statement? The UNIQUE/PRIMARY KEY syntax only allows bare column names, per the SQL standard. While there's not any technical reason why we couldn't extend that syntax, there's a pretty large fear factor involved: we could find ourselves behind the spec-compliance eight-ball if the SQL committee exercises their prerogative to extend the syntax in some other direction. CREATE INDEX is entirely outside the spec (as the whole concept of indexes is outside the spec) and so we can pretty much do what we please within that statement. This is exactly the same reason why we don't support opclass names in UNIQUE/PRIMARY KEY, as has been suggested several times recently, eg http://archives.postgresql.org/pgsql-hackers/2006-03/msg00163.php 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] Accessing composite type columns in indexes
On Mar 4, 2006, at 14:17 , Tom Lane wrote: The UNIQUE/PRIMARY KEY syntax only allows bare column names, per the SQL standard. While there's not any technical reason why we couldn't extend that syntax, there's a pretty large fear factor involved: we could find ourselves behind the spec-compliance eight-ball if the SQL committee exercises their prerogative to extend the syntax in some other direction. Cool. Thanks for the explanation. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Updates to my PostgreSQL Wiki
On Friday 03 March 2006 23:04, Redefined Horizons wrote: I've got some new content on my wiki for PostgreSQL. http://www.bluwiki.org/go/PostgreSQL_Tips_From_The_Sunburned_Surveyor You will find an article on making connections to the PostgreSQL server, and an article on creating automated backups of PostgreSQL databases using a bash shell script. I think you have a typo in the nowDate declaration. It should read: declare nowDate=$(date +%F) -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(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
