[GENERAL] different sort order for primary key index
I have a composite primary key for a table, let's call it (col1, col2). When this table is created, obviously an implicit index is created for this key. I would like the sort order of this index to be different for the two columns -- if I were to create the index myself, I would pass on (col1, col2 DESC). The ALTER INDEX documentation suggests that it's not possible to change the sort order of a column, so I can envision two ways to get around this: 1) create a second UNIQUE index of (col1, col2 DESC), or 2) not define a primary key and just specify a UNIQUE index separately. Primary keys are basically restricted to being unique and non-null, but I'm unclear if PostgreSQL treats primary keys differently from unique, non-null constraints.
[GENERAL] subscribe
subscribe -- Oleg Shalnev (Kalpa Project) -- mailto: o...@kalpa.ru skype: oleg_shalnev sip: 17474845...@gizmo5.com jabber: oleg.shal...@gmail.com icq:366619571 http://kalpa.ru
Re: [GENERAL] Cannot start the postgres service
Hi, I like that "why on earth are you messing about with the data directory when you don't understand what it does and how it works?" Actully I am not a DB person and having almost ZERO knowledge abt it but I am working on an application which takes backup(full & incremental) of diff DB like mysql, postgres... Now I dont have any support who helps me on DB side & still I need to move on with my java code so in current situation I am trying my hands on the DB backups as well since none else has that knowledge so I am doing it by searching on my own :) I had 2 approach in my mind...to sync up transaction log files with specific full backup 1) to keep only time relavent files in pg_xlog dir and move other files to archive dir with code which is not a good idea as u suggest 2) to copy files from pg_xlog & archive dir(which is used by archive_command) and move files from the archive dir & not from the pg_xlog & thus it will be a another direction for solution of inc backup. Moving of files is done my postgres as well so I guess, from the archive dir we can do that. Craig Ringer wrote: > > On 13/10/2009 2:59 PM, Mitesh51 wrote: >> >> Yeah... >> >> My query is...Is it the reason y postgres stops working?? (Moving files >> from >> pg_xlog) > > pg_xlog contains transaction logs. These aren't "log files" in the sense > of text logs designed for the administrator to use. They're part of the > critical function of the database and they're what permits Pg to support > transactional rollback, safe crash recovery, point-in-time recovery, and > lots more. > > Deleting them or moving them is just as bad for your database as > deleting or moving the files that store tables. It's a really, really > bad idea. > > A better question might by "why on earth are you messing about with the > data directory when you don't understand what it does and how it works?". > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25885603.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot start the postgres service
On Tue, Oct 13, 2009 at 11:40 PM, Mitesh51 wrote: > > I like that "why on earth are you messing about with the > data directory when you don't understand what it does and how it works?" > > Actully I am not a DB person and having almost ZERO knowledge abt it but I > am working on an application which takes backup(full & incremental) of diff > DB like mysql, postgres... > > Now I dont have any support who helps me on DB side & still I need to move > on with my java code so in current situation I am trying my hands on the DB > backups as well since none else has that knowledge so I am doing it by > searching on my own :) > > I had 2 approach in my mind...to sync up transaction log files with specific > full backup > > 1) to keep only time relavent files in pg_xlog dir and move other files to > archive dir with code which is not a good idea as u suggest > > 2) to copy files from pg_xlog & archive dir(which is used by > archive_command) and move files from the archive dir & not from the pg_xlog > & thus it will be a another direction for solution of inc backup. > > Moving of files is done my postgres as well so I guess, from the archive dir > we can do that. There are really three reliable ways to take a coherent backup. 1: pg_dump 2: PITR 3: Snapshots. What you're trying seems closer to PITR (Point In Time Recovery). Look it up in the docs see if it makes sense. OTOH, snapshots, combined with some kind of diff utility (rdiff is nice) an provide incrementals quite easily. The deltas may be large if your db changes a lot over time. The cool thing about rdiff is that the latest bu is a full backup, and everything else is deltas going back in time. I.e. instead of just storing a delta, it's applied to the most recent (i.e. full) backup to bring it forward, then store that and the delta to go backwards instead of forwards. Snapshotting methodology is important, it has to make a coherent at an instant in time snapshops or they may not work properly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Case statement with different data types
Hi everyone, I am currently working on an app that is split into several databases with the same table but a column with differing data type. eg. Database 1 tablename: gp column: available - data type boolean Database 2 tablename: gp column: available - data type character(1) I would like to be able to create a query that returns 'T' or 'F' using the same code & query. I have tried a few different cast() options without success - and then tried: SELECT CASE WHEN ((SELECT data_type FROM information_schema.columns WHERE table_name = 'gp' and column_name = 'available') = 'boolean') THEN CASE WHEN (available) THEN 'T' ELSE 'F' END ELSE CASE WHEN (available='T' OR available='t') then 'T' ELSE 'F' END END as available FROM gp When I run this query on the boolean data type database it works correctly - however on the character field I get the error 'ERROR: argument of CASE/WHEN must be type boolean, not type character' I really don't want to go through and change data types just at the moment - can anyone advise any way around this? Many thanks, Gus
Re: [GENERAL] Case statement with different data types
Hello try to explicit cast select 't'::boolean; regards Pavel Stehule 2009/10/14 Gus Waddell : > Hi everyone, > > I am currently working on an app that is split into several databases with > the same table but a column with differing data type. > > eg. Database 1 > tablename: gp > column: available - data type boolean > > Database 2 > tablename: gp > column: available - data type character(1) > > I would like to be able to create a query that returns 'T' or 'F' using the > same code & query. > > I have tried a few different cast() options without success - and then > tried: > > SELECT > CASE > WHEN ((SELECT data_type FROM information_schema.columns WHERE > table_name = 'gp' and column_name = 'available') = 'boolean') THEN > CASE > WHEN (available) THEN 'T' > ELSE 'F' > END > ELSE > CASE > WHEN (available='T' OR available='t') then 'T' > ELSE 'F' > END > END as available > FROM > gp > > > When I run this query on the boolean data type database it works correctly - > however on the character field I get the error 'ERROR: argument of > CASE/WHEN must be type boolean, not type character' > > I really don't want to go through and change data types just at the moment - > can anyone advise any way around this? > > Many thanks, > Gus > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
On Tue, Oct 13, 2009 at 10:22:04PM +, Tim Landscheidt wrote: > Sam Mason wrote: > > Calculating "(C - B) / C" isn't easy for timestamps, whereas it's easy > > for dates. I believe this is why there's a specific version for the > > former but not the latter. > > (I obviously meant "(B - A) / C" :-).) Huh, I hadn't even noticed that! > I would assume > that you just have to convert A, B and C to seconds (since > epoch) and then use a normal integer division. The problem is that the Gregorian calender is far too complicated. For example, think what would happen with an interval of "months". It doesn't help converting to seconds because the length of a month in seconds changes depending on which year the month is in and which month you're actually dealing with. This makes any definition of "division" I've ever been able to think of ill defined and hence the above calculation won't work. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot start the postgres service
On 14/10/2009 2:29 PM, Scott Marlowe wrote: > On Tue, Oct 13, 2009 at 11:24 PM, Craig Ringer > wrote: >> >> A better question might by "why on earth are you messing about with the >> data directory when you don't understand what it does and how it works?". > > Not that anyone wants to discourage exploring. It's just there are > better ways to go about things than deleting / removing files if > you're not sure what they do. Well said. For that matter, there's no harm going and mangling the data directory of an install you don't care about either, though I'm not sure I see what's to be gained by it. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] different sort order for primary key index
Paul Hartley wrote: > I have a composite primary key for a table, let's call it > (col1, col2). When this table is created, obviously an > implicit index is created for this key. I would like the > sort order of this index to be different for the two columns > -- if I were to create the index myself, I would pass on > (col1, col2 DESC). The ALTER INDEX documentation suggests > that it's not possible to change the sort order of a column, > so I can envision two ways to get around this: 1) create a > second UNIQUE index of (col1, col2 DESC), or 2) not define a > primary key and just specify a UNIQUE index separately. > Primary keys are basically restricted to being unique and > non-null, but I'm unclear if PostgreSQL treats primary keys > differently from unique, non-null constraints. I think you can safely go for 2). Although I admit it is not pretty. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] different sort order for primary key index
there are certain conditions where PK is required, but apart from that it is pretty much equivalent of unique not null. Obviously index is created, in order to keep things unique. the (col1, col2 DESC) type of index is useful, when you have query that uses it that way. For example, if your query is to search index backwards, it will be quite slow on some hardware - and adding DESC in index desc, will make postgresql layout the bits on disc that way - which will obviously speed things up.
[GENERAL] Test for optimizer
I want to test the optimizer of postgresql. Can anyone give me any idea about which kinds of query I should test? large query for path an geqo? subquery?
[GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
Hello,
I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of
our application is to "export" and "import" data, and to accomplish this
I've written some functions that use COPY ... TO ... BINARY and COPY ...
FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I
"import" from export files that were created under 8.3.7 the timestamps
are not brought in correctly. I boiled it down to this simple test to
discover where the break-down occurs:
On the 8.3.7 installation I run this:
CREATE TABLE test (
testtime timestamp
);
INSERT INTO test VALUES(now());
COPY test TO 'C:/Temp/test.backup' BINARY;
then, on the 8.4.1 installation I run this:
CREATE TABLE test (
testtime timestamp
);
COPY test FROM 'C:/Temp/test.backup' BINARY;
SELECT * FROM test;
And what goes into the 8.3.7 side (e.g. '2009-10-14 09:10:32.989') comes
out wrong on the 8.4.1 side ('152013-03-31 15:44:27.229979').
The encoding in both databases is the same (WIN1252). I double-checked
and both columns are "timestamp without timezone". Just for kicks I ran
my test (above) using COPY ... CSV, which of course worked because it
writes out plain-text.
I've attached two files, test.837 (the 8.3.7 BINARY COPY from my test
above) and test.841 (a BINARY COPY from 8.4.1 of the "test" table that
had the correct date in it). Both files were created with only one row
in test, using the exact same date/time. So in theory these two files
should be identical. But clearly, 8.3.7 does something differently than
8.4.1. Also, if I try to COPY the 8.4.1 file into 8.3.7 the date is
likewise not correct ('2000-01-01 00:00:00').
So I'm wondering if this is a bug in 8.4.1, or if I've left some stone
unturned. Just if you're wondering, the two installations are in
different worlds (VMs), both running XP sp3.
Thanks so much... John
test.837
Description: test.837
test.841
Description: test.841
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
that's because by default 8.4 uses integer timestamps, instead of whatever 8.3 was using. and you pretty much use something, that is suppose to be only used within the scope of the same version and hardware type (and potentially even build).
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
"Chase, John" writes: > I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of > our application is to "export" and "import" data, and to accomplish this > I've written some functions that use COPY ... TO ... BINARY and COPY ... > FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I > "import" from export files that were created under 8.3.7 the timestamps > are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must have done the build with different options. Would you concur? Maybe I should ask the man behind the curtain (Dave Page). Thanks! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, October 14, 2009 10:21 AM To: Chase, John Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect "Chase, John" writes: > I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of > our application is to "export" and "import" data, and to accomplish this > I've written some functions that use COPY ... TO ... BINARY and COPY ... > FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I > "import" from export files that were created under 8.3.7 the timestamps > are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] different sort order for primary key index
Paul Hartley writes: > ... I'm unclear > if PostgreSQL treats primary keys differently from unique, non-null > constraints. The *only* thing that the system does specially with a primary key constraint is that a PK creates a default column target for foreign key references. For example, create table m (id int primary key); create table s (refid int references m); versus create table m (id int); create unique index mi on m (id); create table s (refid int references m(id)); I have to spell out "(id)" in that last command because there's no PK to establish a default target. Other than that, behavior and performance should be the same. The planner and executor only care about the indexes, not about whatever constraints they might have come from. Likewise, NOT NULL is NOT NULL regardless of what syntax you used to slap it onto the column. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] different sort order for primary key index
On Wed, Oct 14, 2009 at 10:29:56AM -0400, Tom Lane wrote: > Paul Hartley writes: > > ... I'm unclear > > if PostgreSQL treats primary keys differently from unique, non-null > > constraints. > > The *only* thing that the system does specially with a primary key > constraint is that a PK creates a default column target for foreign key > references. It also (silently) overrides any NOT NULL constraint doesn't it? For example: CREATE TABLE x ( id INT NULL PRIMARY KEY ); ends up with "id" being NOT NULL, even though I asked for it to be nullable. Not sure if it's useful for this case to be an error, though it would be more in line with PG throwing errors when you asked for something bad instead of making a best guess. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] different sort order for primary key index
On Wed, Oct 14, 2009 at 3:37 PM, Sam Mason wrote: > On Wed, Oct 14, 2009 at 10:29:56AM -0400, Tom Lane wrote: > > Paul Hartley writes: > > > ... I'm unclear > > > if PostgreSQL treats primary keys differently from unique, non-null > > > constraints. > > > > The *only* thing that the system does specially with a primary key > > constraint is that a PK creates a default column target for foreign key > > references. > > It also (silently) overrides any NOT NULL constraint doesn't it? For > example: > > CREATE TABLE x ( id INT NULL PRIMARY KEY ); > > ends up with "id" being NOT NULL, even though I asked for it to be > nullable. Not sure if it's useful for this case to be an error, though > it would be more in line with PG throwing errors when you asked for > something bad instead of making a best guess. > > if that happens, shouldn't it be an error ? after all it could potentially confuse. -- GJ
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
"Chase, John" writes: > That makes sense, of course. I'm guessing this is because I formally > used the pgInstaller and since 8.4 is not supported yet by pgInstaller I > moved to the EnterpriseDB installer. The man behind the current must > have done the build with different options. Would you concur? Well, there's not much guessing or asking necessary --- try "show integer_datetimes" on both servers. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
Wow, quick response from Dave Page. For those who may be interested, here's his answer: "pgInstaller used floating point, whilst the one-click installers use (and will continue to use) the more accurate integer timestamps." -Original Message- From: Chase, John Sent: Wednesday, October 14, 2009 10:29 AM To: pgsql-general@postgresql.org Subject: RE: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must have done the build with different options. Would you concur? Maybe I should ask the man behind the curtain (Dave Page). Thanks! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, October 14, 2009 10:21 AM To: Chase, John Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect "Chase, John" writes: > I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of > our application is to "export" and "import" data, and to accomplish this > I've written some functions that use COPY ... TO ... BINARY and COPY ... > FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I > "import" from export files that were created under 8.3.7 the timestamps > are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
On 14/10/2009 15:28, Chase, John wrote: > That makes sense, of course. I'm guessing this is because I formally > used the pgInstaller and since 8.4 is not supported yet by pgInstaller I > moved to the EnterpriseDB installer. The man behind the current must As I understand it, pgInstaller is going to be maintained for pre-8.4 versions only; the only installer for 8.4+ is EnterpriseDB's one-click installer. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] could not open process token: error code 5
Hi We have an Postgresql 8.2.5 installation on a Windows server 2003 that have worked perfectly for our Mediawiki until we tried to update to 8.4. Before the update we took a backup, stopped the service and took a copy of the entire database catalog. We could not make the 8.4 (installed in a different directory) work so we decided to go back to the initial installation which remained intact. Then when we try to start the service it fails and we get the message "could not open process token: error code 5" in the event viewer, nothing else. Now after googling for some hours and days I am stuck. the Postgres user are to start the service and so nothing is changed there either. Even though the database files were not changed, we have also copied the entire original database back. The installation has been done with the "postgresql-8.2-int.msi" package and it has been reapplied with the following command, "msiexec /i postgresql-8.2-int.msi REINSTALLMODE=vamus REINSTALL=ALL /" Still no progress. What to do? /Anders -- View this message in context: http://www.nabble.com/could-not-open-process-token%3A-error-code-5-tp25891332p25891332.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What does "INSERT 0 1" mean?
Hi, I can't find what does INSERT 0 1 mean. "1" stands for the number of the records added to the table, as far as I understood, but what about the "0"? Thank you very much. -- View this message in context: http://www.nabble.com/What-does-%22INSERT-0-1%22-mean--tp25892901p25892901.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What does "INSERT 0 1" mean?
On 14/10/2009 16:05, [.::MDT::.] wrote: > Hi, > I can't find what does > INSERT 0 1 > mean. > > "1" stands for the number of the records added to the table, as far as I > understood, but what about the "0"? It stands for the OID of the row that was inserted, if the table was created to use them (CREATE TABLE WITH (OIDS=TRUE)); newer versions of PostgreSQL by default have tables created without OIDs on the rows, so you just get a 0 returned instead. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What does "INSERT 0 1" mean?
On Wednesday 14 October 2009 11:05, [.::MDT::.] wrote: > Hi, > I can't find what does > INSERT 0 1 > mean. > > "1" stands for the number of the records added to the table, as far as I > understood, but what about the "0"? > > Thank you very much. > -- > View this message in context: > http://www.nabble.com/What-does-%22INSERT-0-1%22-mean--tp25892901p25892901. >html Sent from the PostgreSQL - general mailing list archive at Nabble.com. It represents the OID, which PostgreSQL, in earlier versions, generated by default. Later versions do not do this by default. The zero indicates that you are not generating OID's for that table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
On Tue, Oct 13, 2009 at 5:12 PM, Tim Landscheidt
wrote:
> Peter Hunsberger wrote:
>
> You can either use a PL/pgSQL function ("SETOF TEXT" just
> for the convenience of the example):
That works well, takes about 20 seconds to do the 6M+ rows
>
> or a recursive query (which I always find very hard to com-
> prehend):
>
> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS
> | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
> | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE
> c.col = p.Value + 1)
> | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
> | GROUP BY LeftBoundary
> | ORDER BY LeftBoundary;
>
> Could you run both against your data set and find out which
> one is faster for your six million rows?
>
Turns out the server is v 8.3, looks like I need to get them to
upgrade it so I get recursive and windowing :-(. If this happens any
time soon I'll let you know the results.
Many thanks.
--
Peter Hunsberger
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 3d Vector Types and operators
Hi,
I cant find in the documentation support for a 3 dimensional vector,
I have only seen the array type, I am interested in doing vector dot
products and vector cross products, also summing vectors and
multiplying by a scalar quantity
select array[1,2,3]+array[2,4,5];
select 2*array[1,2,3];
The error message is:
No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Has anyone tried to do this before?
Has anyone written operators for this?
I have got as far as
CREATE or replace FUNCTION add(anyarray, anyarray) RETURNS anyarray
AS 'select array[$1[1] + $2[1],$1[2] + $2[2],$1[3] + $2[3]];'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
drop FUNCTION dot(anyarray, anyarray);
CREATE or replace FUNCTION dot(anyarray, anyarray) RETURNS int
AS 'select $1[1] * $2[1]+$1[2] * $2[2]+$1[3] * $2[3];'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
It works for integer arrays:
select add(array[1,2,3],array[2,4,5]);
add
-
{3,6,8}
(1 row)
select dot(array[1,2,3],array[2,4,5]);
dot
-
25
but it gives me an error for a floating point array
epm=# select add(array[1.2,2,3],array[2,4,5]);
ERROR: function add(numeric[], integer[]) does not exist
LINE 1: select add(array[1.2,2,3],array[2,4,5]);
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
How can I fix it to cope with real or integer arrays?
How could I change this to use operators?
Is it efficient? Can it be made more efficient?
Thanks in advance
Andy Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How ad an increasing index to a query result?
Hello,
Could somebody please try to help me with this problem?
So, let’s say that I have the query:
CREATE SEQUENCE c START 1;
SELECT a, nextval('c') as b
FROM table1
ORDER BY a DESC LIMIT 5;
I.e., I want to pick the 5 largest entries from table1 and show them
alongside a new index column that tells the position of the entry. For
example:
a | b
82 | 5
79 | 4
34 | 3
12 | 2
11 | 1
However, when I try this approach, the values of column b don’t follow
the correct order. How should I go about and modify my code?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 3d Vector Types and operators
On Wed, Oct 14, 2009 at 12:04:26PM -0500, Andrew Bailey wrote: > I cant find in the documentation support for a 3 dimensional vector, > I have only seen the array type, I am interested in doing vector dot > products and vector cross products, also summing vectors and > multiplying by a scalar quantity If you did do this, I'd be tempted to use something like: create type point3d AS ( x float8, y float8, z float8 ); and then write your functions using this. The length of an array isn't part of its type and so PG wouldn't be able to stop you from writing: select array[1,2,3] + array[2,3,4,5,6]; if you provided the appropriate operators. If you use a fixed sized tuple, as above, you'd get errors if you tried to use points of the wrong dimensionality. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How ad an increasing index to a query result?
I found an article that should help you with the answer:
http://explainextended.com/2009/05/05/postgresql-row-numbers/
ROWNUM is a very useful pseudocolumn in Oracle that returns the
position of each row in a final dataset.
Upcoming PostgreSQL 8.4 will have this pseudocolumn, but as for now
will we need a hack to access it. (in 8,3)
The main idea is simple:
1. Wrap the query results into an array
2. Join this array with a generate_series() so that numbers from 1
to array_upper() are returned
3. For each row returned, return this number (as ROWNUM) along the
corresponding array member (which is the row from the original query)
...
See original article for the code
Hope it helps
Andy Bailey
On Wed, Oct 14, 2009 at 12:05 PM, Josip wrote:
> Hello,
>
> Could somebody please try to help me with this problem?
> So, let’s say that I have the query:
>
> CREATE SEQUENCE c START 1;
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
>
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
> However, when I try this approach, the values of column b don’t follow
> the correct order. How should I go about and modify my code?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Andrew Bailey
(312) 866 9556
NOTA DE CONFIDENCIALIDAD Y DE NO DIVULGACIÓN:
La información contenida en este E-mail y sus archivos adjuntos es
confidencial y sólo puede ser utilizada por el individuo
o la empresa a la cual está dirigido. Si no es el receptor autorizado,
cualquier retención, difusión,
distribución o copia de este mensaje queda prohibida y sancionada por
la ley. Si por error recibe este
mensaje, favor devolverlo y borrarlo inmediatamente.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How ad an increasing index to a query result?
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
>
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
[Spotts, Christopher]
Sounds like you you want 8.4 and windowing functions like row_number().
(http://www.postgresql.org/docs/8.4/static/functions-window.html)
If you have 8.4.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot start the postgres service
Mitesh51 wrote: > I had 2 approach in my mind...to sync up transaction log files with specific > full backup > > 1) to keep only time relavent files in pg_xlog dir and move other files to > archive dir with code which is not a good idea as u suggest Postgres is prepared to (and assumes it can) reuse and delete files in pg_xlog. If you need a copy you can use for your own purposes, you MUST get it through an archive_command. You MUST NOT fiddle with the files in pg_xlog directly. Also note that your archive_command needs to create a separate copy of the file. Hardlinks are not allowed, because the file might get rewritten by Postgres later. Moving (mv) the original files is not allowed either for the same reason. Postgres will leave the file alone until it has been archived, and assumes it can do whatever it pleases with it as soon as the archiver has returned success (exit code 0). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioned table question
So we know have data in ~30 partitioned tables. Our requirements now necessitate adding some columns to all these tables ( done ) which will get populated via batch sql for the older tables and by normal processing as we move forward. The batch update is going to result in dead tuples in the older tables. What would be the recommended way to recover this dead space? Vacuum full children tables + reindex children tables? or Thanks, reid -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioned table question
On Wednesday 14 October 2009, Reid Thompson wrote: > So we know have data in ~30 partitioned tables. > Our requirements now necessitate adding some columns to all these tables > ( done ) which will get populated via batch sql for the older tables and > by normal processing as we move forward. > > The batch update is going to result in dead tuples in the older tables. > What would be the recommended way to recover this dead space? > Vacuum full children tables + reindex children tables? or > cluster's faster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not open process token: error code 5
On Wednesday 14 October 2009 6:42:39 am Andale wrote: > Hi > > We have an Postgresql 8.2.5 installation on a Windows server 2003 that have > worked perfectly for our Mediawiki until we tried to update to 8.4. Before > the update we took a backup, stopped the service and took a copy of the > entire database catalog. We could not make the 8.4 (installed in a > different directory) work so we decided to go back to the initial > installation which remained intact. > > Then when we try to start the service it fails and we get the message > "could not open process token: error code 5" in the event viewer, nothing > else. Now after googling for some hours and days I am stuck. the Postgres > user are to start the service and so nothing is changed there either. Even > though the database files were not changed, we have also copied the entire > original database back. The installation has been done with the > "postgresql-8.2-int.msi" package and it has been reapplied with the > following command, "msiexec /i postgresql-8.2-int.msi REINSTALLMODE=vamus > REINSTALL=ALL /" > > Still no progress. > > What to do? > > /Anders Have you tried getting rid of the data directory you copied back, doing an initdb to create a new fresh data directory and the restoring from the backup? Just to cover the case where you did not copy everything you needed to when you made the original copy. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
Peter Hunsberger wrote: > [...] >> or a recursive query (which I always find very hard to com- >> prehend): >> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS >> | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t) >> | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE >> c.col = p.Value + 1) >> | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols >> | GROUP BY LeftBoundary >> | ORDER BY LeftBoundary; >> Could you run both against your data set and find out which >> one is faster for your six million rows? > Turns out the server is v 8.3, looks like I need to get them to > upgrade it so I get recursive and windowing :-(. If this happens any > time soon I'll let you know the results. > Many thanks. After some tests with a data set of 7983 rows (and 1638 ran- ges): Don't! :-) The recursive solution seems to be more than double as slow as the iterative. I'll take it to -per- formance. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to Export ALL plpgsql functions/triggers to file
Hi, Could you tell me how to Export ALL plpgsql functions/triggers to file? Thank you
Re: [GENERAL] Query to find contiguous ranges on a column
On Wed, Oct 14, 2009 at 4:50 PM, Tim Landscheidt wrote: > Peter Hunsberger wrote: > > After some tests with a data set of 7983 rows (and 1638 ran- > ges): Don't! :-) The recursive solution seems to be more > than double as slow as the iterative. I'll take it to -per- > formance. > Interesting, I've never liked recursive on Oracle but performance is usually reasonable... Thanks for the heads up... -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SFPUG: Video from "Statistics and Postgres -- How the Planner Sees Your Data" Now on Vimeo
Hi, The video from "Statistics and Postgres — How the Planner Sees Your Data," the September 8, 2009 meeting of the SFPUG, is now available on Vimeo: http://vimeo.com/7051082 -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG 8.4 and pg_autovacuum functionality
Marcelo wrote: > Hello, > > Since pg_autovacuum no longer exits on PG 8.4 and it seems that one > now needs to provide the storage parameters during CREATE TABLE or > later on with an ALTER TABLE. > Will that ALTER TABLE block anything going on that table until it's > finished ? I assume not since no table data is actually being > rewritten. It will block until it is finished (just like any other ALTER TABLE), but unless there's something else blocking the table for a long time, it should be very short. No data is being rewritten. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not open process token: error code 5
Hello. I had such an error but rather long ago. Unfortunately I don't remember all the details but you could try to do the followig: - check if the directory 'data' has read/write rights for your OS account (account under which you try to start postgres). - check if your OS account has the right to log on as service (Administrative Tools/Local Security Settings/User Rights Assignment) - check in Computer Management/Local Users and Groups/Users if your OS account is NOT the member of any group of users. Regards, Marina. > > Hi > > We have an Postgresql 8.2.5 installation on a Windows server 2003 that have > worked perfectly for our Mediawiki until we tried to update to 8.4. Before > the update we took a backup, stopped the service and took a copy of the > entire database catalog. We could not make the 8.4 (installed in a different > directory) work so we decided to go back to the initial installation which > remained intact. > > Then when we try to start the service it fails and we get the message "could > not open process token: error code 5" in the event viewer, nothing else. Now > after googling for some hours and days I am stuck. the Postgres user are to > start the service and so nothing is changed there either. Even though the > database files were not changed, we have also copied the entire original > database back. The installation has been done with the > "postgresql-8.2-int.msi" package and it has been reapplied with the > following command, "msiexec /i postgresql-8.2-int.msi REINSTALLMODE=vamus > REINSTALL=ALL /" > > Still no progress. > > What to do? > > /Anders > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
