[SQL] Compiling pl/pgsql functions
Hi, I'm responsable for the database here in the company, and I like to know if is there a way to compile my pl/pgsql functions, its not a performance problem, it is more a security problem, i don like to have somebody looking into my codes and see the company rules. Is there a way to do that, or the only way is writting my functions in C?? Thanks for any help and regards to all!!! = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Compiling pl/pgsql functions
So use Grant more wisely. - Original Message - From: "Rodrigo Sakai" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 19, 2004 2:44 PM Subject: [SQL] Compiling pl/pgsql functions >Hi, I'm responsable for the database here in the company, and I like to know if is there a way to compile my pl/pgsql functions, its not a performance problem, it is more a security problem, i don like to have somebody looking into my codes and see the company rules. > Is there a way to do that, or the only way is writting my functions in C?? > > Thanks for any help and regards to all!!! > > > > = > Rodrigo Sakai > Database Programmer > [EMAIL PROTECTED] > http://www.2bfree.com.br > Tel: (55) (11) 5083-5577 > Fax: (55) (11) 5549-3598 > = > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] crosstabs
Hello,
I need to do something similar to a cross tabulation, but without any
aggregation.
Here is an example:
-- TABLE DEFINITION
CREATE TABLE public.type
(
code char(1) PRIMARY KEY,
type varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.master
(
id int4 PRIMARY KEY,
master_name varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.detail
(
id int4 PRIMARY KEY,
code_type char(1) REFERENCES public.type,
id_master int4 REFERENCES public.master,
detail_name varchar(30)
) WITHOUT OIDS;
-- DATA
INSERT INTO public.type VALUES('A', 'TA');
INSERT INTO public.type VALUES('B', 'TB');
INSERT INTO public.type VALUES('C', 'TC');
INSERT INTO public.master VALUES(1, 'M1');
INSERT INTO public.master VALUES(2, 'M2');
INSERT INTO public.master VALUES(3, 'M3');
INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');
INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');
INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');
INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');
INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');
INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');
INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');
INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');
INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');
-- QUERY
SELECT
master_name,
detail_name,
type
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
The result of that is:
--
master_name | detail_name | type |
--
M1 | M1, D1 | TA |
M1 | M1, D2 | TB |
M1 | M1, D3 | TA |
M1 | M1, D4 | TC |
M2 | M2, D1 | TC |
M3 | M3, D1 | TA |
M3 | M3, D2 | TA |
M3 | M3, D3 | TB |
M3 | M3, D4 | TA |
M3 | M3, D5 | TB |
M3 | M3, D6 | TC |
M3 | M3, D7 | TC |
--
I need something like this:
master_name | TA | TB | TC |
M1 | M1, D1 |||
M1 || M1, D2 ||
M1 | M1, D3 |||
M1 ||| M1, D4 |
M2 ||| M2, D1 |
M3 | M3, D1 |||
M3 | M3, D2 |||
M3 || M3, D3 ||
M3 | M3, D4 |||
M3 || M3, D5 ||
M3 ||| M3, D6 |
M3 ||| M3, D7 |
Does anyone know how to do that in Postgresql? I run version 7.3.4.
Thanks for any idea you might have.
Philippe Lang
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Compiling pl/pgsql functions
On Thu, 19 Feb 2004, Rodrigo Sakai wrote: >Hi, I'm responsable for the database here in the company, and I like >to know if is there a way to compile my pl/pgsql functions, its not a >performance problem, it is more a security problem, i don like to >have somebody looking into my codes and see the company rules. AFAIK there's not much you can do for obfuscation of pl functions right now since someone will be able to see the src text in pg_proc. However, are you allowing people that you don't want to see the code access to write arbitrary sql to the database? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Distributed Transactions
On Wed, 18 Feb 2004, George A.J wrote: > Hi all, > > i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. > is there a transaction coordinator available for Postgres.. there isn't one, really, but you could likely roll something that worked. Oh, and update your version of postgresql. There's a nasty "won't startup" bug in 7.3.2 you'd just as soon rather avoid. It doesn't lose data, but there's nothing like restarting your database only to have it complain about some boundary condition in the write ahead log and then have to spend an hour or so with it down while you download the updates to get it working. 7.3.5 is the latest version of the 7.3 branch, but it looks like rpm wise, the latest on the sites is 7.3.4. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] crosstabs
Here is a solution:
-- QUERY
SELECT
master_name,
CASE WHEN type = 'TA' THEN detail_name END as TA,
CASE WHEN type = 'TB' THEN detail_name END as TB,
CASE WHEN type = 'TC' THEN detail_name END as TC
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
I'm not sure if we could do that without CASE... END...
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Philippe Lang
Envoyé : jeudi, 19. février 2004 17:02
À : [EMAIL PROTECTED]
Objet : [SQL] crosstabs
Hello,
I need to do something similar to a cross tabulation, but without any aggregation.
Here is an example:
-- TABLE DEFINITION
CREATE TABLE public.type
(
code char(1) PRIMARY KEY,
type varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.master
(
id int4 PRIMARY KEY,
master_name varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.detail
(
id int4 PRIMARY KEY,
code_type char(1) REFERENCES public.type,
id_master int4 REFERENCES public.master,
detail_name varchar(30)
) WITHOUT OIDS;
-- DATA
INSERT INTO public.type VALUES('A', 'TA');
INSERT INTO public.type VALUES('B', 'TB');
INSERT INTO public.type VALUES('C', 'TC');
INSERT INTO public.master VALUES(1, 'M1');
INSERT INTO public.master VALUES(2, 'M2');
INSERT INTO public.master VALUES(3, 'M3');
INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');
INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');
INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');
INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');
INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');
INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');
INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');
INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');
INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');
-- QUERY
SELECT
master_name,
detail_name,
type
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
The result of that is:
--
master_name | detail_name | type |
--
M1 | M1, D1 | TA |
M1 | M1, D2 | TB |
M1 | M1, D3 | TA |
M1 | M1, D4 | TC |
M2 | M2, D1 | TC |
M3 | M3, D1 | TA |
M3 | M3, D2 | TA |
M3 | M3, D3 | TB |
M3 | M3, D4 | TA |
M3 | M3, D5 | TB |
M3 | M3, D6 | TC |
M3 | M3, D7 | TC |
--
I need something like this:
master_name | TA | TB | TC |
M1 | M1, D1 |||
M1 || M1, D2 ||
M1 | M1, D3 |||
M1 ||| M1, D4 |
M2 ||| M2, D1 |
M3 | M3, D1 |||
M3 | M3, D2 |||
M3 || M3, D3 ||
M3 | M3, D4 |||
M3 || M3, D5 ||
M3 ||| M3, D6 |
M3 ||| M3, D7 |
Does anyone know how to do that in Postgresql? I run version 7.3.4.
Thanks for any idea you might have.
Philippe Lang
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] crosstabs
On Thursday 19 February 2004 16:02, Philippe Lang wrote: > Hello, > > I need to do something similar to a cross tabulation, but without any > aggregation. Have a look in the contrib/ folder of the source distro (or your contrib package). There are some set-returning tablefunc examples. Also you might want to look at the excellent set-returning function article on http://techdocs.postgresql.org/ -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] DISTINCT ON troubles
Hi, I have a query that reads: SELECT DISTINCT ON (messageboard.threadid) messageboard.threadid, messageboard.topic, owner.ownerid, owner.username FROM messageboard, owner WHERE messageboard.ownerid=owner.ownerid AND messageboard.leagueid = '$leagueid' ORDER BY messageboard.messageid DESC LIMIT $entries_on_page OFFSET $beginThread" The purpose of this query is to retrieve unique threadid's in the order of the most recent posts that have been made to each thread. When I use this query I get an error that: "SELECT DISTINCT ON expressions must match initial ORDER BY expressions". Of course, if I put ORDER BY threadid in the query it would order it in the order that the thread was created, not in the last post made. I have tried using GROUP BY threadID, I get a similar order. Am I just approaching this all wrong and need to create a temporary table and draw from that, or is there a way to salvage this query? Thanks so much, Jeremy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Compiling pl/pgsql functions
>AFAIK there's not much you can do for obfuscation of pl functions right >now since someone will be able to see the src text in pg_proc. However, >are you allowing people that you don't want to see the code access to >write arbitrary sql to the database? Let me explain myself a little better. Actualy we sell software, and some codes of the systems we develope here are inside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that are in the postgresql. Some of our clientes, need that a employee of them get total access to the database instaled locally, becoming the database administrator. Thats ok, but to protect our postgresql codes (functions) i like to compile my plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not be able to get our codes. I insist in my question, is there a way to compile the plpgsql codes or something like that, or its better to think about writting this postgres functions in C?? Thanks for all!!! = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
I guess I am asking about the cast sequence from
char(n) to text.
(' '::char(n))::text trims spaces. This is wrong, imo.
' '::text does not trim spaces.
' '::char(n) does not trim spaces and pads.
char(n) should not trim spaces, right? And
it doesn't on an insert. Text does not trim spaces.
Somewhere the space trimming occurs.
If it is in the operator || then the operator is wrong.
If char(n) is properly defined to not trim spaces then
there should be a separate cat for char(n). It is correct
for it to behave differently than cat for text and varchar
because of the different trimming behaviour.
I can do this patch if there is agreement. But
I may not be able to do it immediately.
elein
On Wed, Feb 18, 2004 at 11:58:37PM -0500, Tom Lane wrote:
> elein <[EMAIL PROTECTED]> writes:
> > So exactly what is the order of casts that produces
> > different results with:
>
> > 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
>
> > Are operators being invoked both (text,text)?
>
> The only relevant operator is "text || text" (there are also some ||
> operators for arrays, bytea, and BIT, but these will all be discarded
> as not the most plausible match). Therefore, in your first example the
> unspecified literals will all be presumed to be text, so the space does
> not get trimmed.
>
> One of the things we could think about as a way to tweak the behavior is
> creating "||" variants that are declared to accept char(n) on one or
> both sides. These could actually use the same C implementation function
> (textcat) of course. But declaring them that way would suppress the
> invocation of rtrim() as char-to-text conversion.
>
> However, if we did that then "||" would behave differently from other
> operators on character strings, so it doesn't seem like a very
> attractive option to me.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] crosstabs
Philippe Lang wrote: I need to do something similar to a cross tabulation, but without any aggregation. See the crosstab() function found in contrib/tablefunc Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Compiling pl/pgsql functions
Rodrigo Sakai wrote: I insist in my question, is there a way to compile the plpgsql codes or something like that no think about writting this postgres functions in C?? yes Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Compiling pl/pgsql functions
Rodrigo, > I insist in my question, is there a way to compile the plpgsql codes or something like that, or its better to think about writting this postgres functions in C?? No, there is not. Nor is there likely to be for any PL, as it would add significant overhead for no real gain. You have, as I see it, 3 choices: 1) You can give up on code obfuscation and simply provide the functions normally, and rely on your contracts and copyright law to protect your code. This is what I do, and I feel pretty strongly that code obfuscation is a dumb and ineffective way to protect copyright. Personally, I find it hard to believe that any of my PL/SQL functions (or yours) are so brilliant that they need trade secret protection. 2) You can write your functions in C and compile them. 3) You can carefully engineer your database permissions so that the user can have almost full DBA powers without being the superuser, and deny them direct access to the pg_proc table. This would be a real PITA, though. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] DISTINCT ON troubles
Jeremy, > Am I just approaching this all wrong and need to create a temporary table > and draw from that, or is there a way to salvage this query? Think about using a subquery instead of the DISTINCT ON approach. I don't think you can get what you want with DISTINCT ON. A temporary table is not necessary. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] DISTINCT ON troubles
Thanks Josh, I'll do that, I just wasn't sure if I was missing something obvious. Jeremy -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] DISTINCT ON troubles Jeremy, > Am I just approaching this all wrong and need to create a temporary table > and draw from that, or is there a way to salvage this query? Think about using a subquery instead of the DISTINCT ON approach. I don't think you can get what you want with DISTINCT ON. A temporary table is not necessary. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
elein <[EMAIL PROTECTED]> writes: > Somewhere the space trimming occurs. The cast from char(n) to text (or varchar) is what's doing the trimming in 7.4. I think you can mostly revert the change by changing that pg_cast entry to specify no conversion function instead of rtrim(). However that would probably result in squirrely, non-spec behavior for comparisons. > If char(n) is properly defined to not trim spaces then > there should be a separate cat for char(n). Possibly, but I think that is considering the issue much too narrowly. Concatenation is not the only textual operator. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] DISTINCT ON troubles
( sorry if this is a repeat, my mail server is being wonky today ) I'm looking for a way, within SQL, given a starting date and an ending date, to get back the number of months between the start and end date. If I "SELECT end_date - start_date", I get back an interval in days; I need months. Thanks for any suggestions, Brian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Compiling pl/pgsql functions
Rodrigo Sakai wrote: AFAIK there's not much you can do for obfuscation of pl functions right now since someone will be able to see the src text in pg_proc. However, are you allowing people that you don't want to see the code access to write arbitrary sql to the database? Let me explain myself a little better. Actualy we sell software, and some codes of the systems we develope here are inside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that are in the postgresql. Some of our clientes, need that a employee of them get total access to the database instaled locally, becoming the database administrator. Thats ok, but to protect our postgresql codes (functions) i like to compile my plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not be able to get our codes. I insist in my question, is there a way to compile the plpgsql codes or something like that, or its better to think about writting this postgres functions in C?? Security through obscurity? Why do those people you want to hide your code from direct SQL access to the database in the first place? Jan Thanks for all!!! = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DISTINCT ON troubles
On Thu, 19 Feb 2004, Brian Knox wrote:
> ( sorry if this is a repeat, my mail server is being wonky today )
>
> I'm looking for a way, within SQL, given a starting date and an ending
> date, to get back the number of months between the start and end date.
> If I "SELECT end_date - start_date", I get back an interval in days; I
> need months.
Maybe date_part?
select date_part('month','2004-08-02 12:00:00'::timestamp) -
date_part('month','2004-05-01 12:00:00'::timestamp);
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] DISTINCT ON troubles
On Thu, 19 Feb 2004, scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
>
> > ( sorry if this is a repeat, my mail server is being wonky today )
> >
> > I'm looking for a way, within SQL, given a starting date and an ending
> > date, to get back the number of months between the start and end date.
> > If I "SELECT end_date - start_date", I get back an interval in days; I
> > need months.
>
> Maybe date_part?
>
> select date_part('month','2004-08-02 12:00:00'::timestamp) -
> date_part('month','2004-05-01 12:00:00'::timestamp);
Note that I think you need a +1 at the end of that...
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Distributed Transactions
"scott.marlowe" <[EMAIL PROTECTED]> writes: > Oh, and update your version of postgresql. There's a nasty "won't > startup" bug in 7.3.2 you'd just as soon rather avoid. Actually I think the "won't start" bug was in 7.3.3 :-(. Still, it's good advice to keep up with the latest version in your release series. We don't make such releases just to amuse ourselves. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Help refining/eliminating recursive selects
I have the following table: create table test ( idserial primary key, product integer, tx_date date, quantity integer) with the following data: id | product | tx_date | quantity +-++-- 1 | 1 | 2004-01-01 | 10 2 | 2 | 2004-01-01 |8 3 | 3 | 2004-01-01 |7 4 | 4 | 2004-01-01 | 12 5 | 1 | 2004-01-15 |9 6 | 2 | 2004-01-15 | 12 7 | 3 | 2004-01-15 |8 8 | 5 | 2004-01-07 | 15 what I want to do is to find the most recent record for each product in the table. The only ways I seem to be able to achieve this is by one of the following 1) A self join: SELECT * FROM test JOIN (SELECT product, max(tx_date) AS tx_date FROM test GROUP BY product) x USING(product, tx_date); 2) A correlated subquery: SELECT * FROM test t WHERE tx_date = ( SELECT max(tx_date) FROM test WHERE product = t.product); or 3) a two-part select: SELECT product, max(tx_date) AS tx_date INTO TEMP TABLE t_prod_date FROM test GROUP BY product; SELECT * FROM test JOIN t_prod_date USING(product, tx_date); I can't help but feel like I'm missing something simple that would do what I want and not mean I need to scan the table multiple times. Is there a better way? In trying to answer some questions in advance: The two-part select _IS_ comparitively slow on the above dataset. In my actual situation, I have about 300 possible products and over 20,00 records to sort through. In that case the overhead of creating the temp table is easily overcome by reducing the search space. In my case the two-part select runs in about 2/3 the time the self-join. Note that we are currently talking about .5 and .3 seconds, but the dataset is growing. The correlated-subquery on the large dataset is horribly slow, comparitively speaking, at about 8 seconds. I'm trying various index approaches, (ANALYZING after adding/dropping an index) with no luck, yet. Maybe I just haven't hit on the right combination of fields to index on? If anybody can either (a) point me in a better direction, or (b) confirm my approach, I would greatly appreciate it Thanks very much. -- Edmund Bacon <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help refining/eliminating recursive selects
On Thu, 19 Feb 2004, Edmund Bacon wrote: > > I have the following table: > > create table test ( > idserial primary key, > product integer, > tx_date date, > quantity integer) > > with the following data: > id | product | tx_date | quantity > +-++-- > 1 | 1 | 2004-01-01 | 10 > 2 | 2 | 2004-01-01 |8 > 3 | 3 | 2004-01-01 |7 > 4 | 4 | 2004-01-01 | 12 > 5 | 1 | 2004-01-15 |9 > 6 | 2 | 2004-01-15 | 12 > 7 | 3 | 2004-01-15 |8 > 8 | 5 | 2004-01-07 | 15 > > > what I want to do is to find the most recent record for each product in > the table. If you don't mind using a PostgreSQL extension, I think distinct on might help you. Maybe something like the following: select distinct on (product) * from test order by product desc, tx_date desc; This might be helped by an index on (product, tx_date). ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Help refining/eliminating recursive selects
What about something like this: select * from test order by tx_date desc limit 1 Yudie - Original Message - From: "Edmund Bacon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 19, 2004 5:09 PM Subject: [SQL] Help refining/eliminating recursive selects I have the following table: create table test ( idserial primary key, product integer, tx_date date, quantity integer) with the following data: id | product | tx_date | quantity +-++-- 1 | 1 | 2004-01-01 | 10 2 | 2 | 2004-01-01 |8 3 | 3 | 2004-01-01 |7 4 | 4 | 2004-01-01 | 12 5 | 1 | 2004-01-15 |9 6 | 2 | 2004-01-15 | 12 7 | 3 | 2004-01-15 |8 8 | 5 | 2004-01-07 | 15 what I want to do is to find the most recent record for each product in the table. The only ways I seem to be able to achieve this is by one of the following 1) A self join: SELECT * FROM test JOIN (SELECT product, max(tx_date) AS tx_date FROM test GROUP BY product) x USING(product, tx_date); 2) A correlated subquery: SELECT * FROM test t WHERE tx_date = ( SELECT max(tx_date) FROM test WHERE product = t.product); or 3) a two-part select: SELECT product, max(tx_date) AS tx_date INTO TEMP TABLE t_prod_date FROM test GROUP BY product; SELECT * FROM test JOIN t_prod_date USING(product, tx_date); I can't help but feel like I'm missing something simple that would do what I want and not mean I need to scan the table multiple times. Is there a better way? In trying to answer some questions in advance: The two-part select _IS_ comparitively slow on the above dataset. In my actual situation, I have about 300 possible products and over 20,00 records to sort through. In that case the overhead of creating the temp table is easily overcome by reducing the search space. In my case the two-part select runs in about 2/3 the time the self-join. Note that we are currently talking about .5 and .3 seconds, but the dataset is growing. The correlated-subquery on the large dataset is horribly slow, comparitively speaking, at about 8 seconds. I'm trying various index approaches, (ANALYZING after adding/dropping an index) with no luck, yet. Maybe I just haven't hit on the right combination of fields to index on? If anybody can either (a) point me in a better direction, or (b) confirm my approach, I would greatly appreciate it Thanks very much. -- Edmund Bacon <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] pg_restore - don´t restore. Why?
pg_dump, ok. pg_restore, don´t restore. Why? Red Hat 9, Fedora, Conectiva 9 and Postgres 7.4.1 make my ambient use. pg_restore -i -v -N -etc... return: Droping... error! Object does not exist... OR Creating... error! Object exist with the same arguments... AND "Break the restore process". Is this errors just in time for break the restore vital process?
