Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as

[Ragnar Hafstað]

On Tue, 2005-05-17 at 23:16 -0300, Alain wrote:
> 
> Ragnar Hafstað escreveu:
> >>[how to solve the get next 100 records problem]

> BUT, I think that this is close to a final solution, I made some 
> preliminary test ok. Please tell me what you think about this.
> 
> Fisrt let's state that I am reading records to put on a screen (in a 
> Table/Grid). I separated the problem is *3* parts
> 
> -first select is as above:
> select ... from tab ORDER by skey,pkey LIMIT 100;
> 
> -second method for next 100:
> select ... from tab WHERE skey>=skey_last
>  ORDER BY skey,pkey
>  LIMIT 100;
> but here I test for repetitions using pkey and discard them
> 
> -now if I get all repetitions or the last 100 have the same skey with 
> the second method, I use
> select ... from tab WHERE skey=skey_last AND pkey>pkey_last
>  ORDER BY skey,pkey
>  LIMIT 100;
> until I get an empty response, then I go back to the second method.

if your distribution is such that those skeys that have > 100 records
tend to have a lot more, you might have a higher limit for this case.


> All queries are extremely fast with 600 records and it looks like 
> the few redundant or empty queries (but very fast) will not be a problem.
> 
> What is your opinion about this (apart that it is a bit complex :) ??


looks fine

gnari



---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Changed to: how to solve the get next 100 records problem

[Alain]

Hi Ragnar (and others),
I found something that is both fast and simple (program side):
Allways assuming that: pkey is a primary key and skey is a sort key, and
there exists an index on (skey,pkey)
first select is
select ... from tab ORDER by skey,pkey LIMIT 100;
subsequent selects are
(select ... from tab WHERE skey=skey_last AND pkey>pkey_last
 ORDER BY skey,pkey LIMIT 100)
UNION
(select ... from tab WHERE skey>skey_last
 ORDER BY skey,pkey LIMIT 100)
ORDER BY skey,pkey LIMIT 100;
The catch is that if the first select would have more than 100 records 
and was limited to 100, the second select's data is completeply 
discarted by the 3rd limit!

The only strange thing is that without the 3rd order by, the order is 
wrong. I didn't expect it because each select is created ordered. Is it 
expected that UNION mixes it all up? (using postgre 7.4.1)

The 3rd order by is not indexed, but it operates in a memory table of no 
more than 200 so it is fast too.

Please comment on this. I tested  and it worked but I really new to sql 
and I feel insecure...

Thanks,
Alain



[how to solve the get next 100 records problem]

BUT, I think that this is close to a final solution, I made some 
preliminary test ok. Please tell me what you think about this.

Fisrt let's state that I am reading records to put on a screen (in a 
Table/Grid). I separated the problem is *3* parts

-first select is as above:
select ... from tab ORDER by skey,pkey LIMIT 100;
-second method for next 100:
select ... from tab WHERE skey>=skey_last
ORDER BY skey,pkey
LIMIT 100;
but here I test for repetitions using pkey and discard them
-now if I get all repetitions or the last 100 have the same skey with 
the second method, I use
select ... from tab WHERE skey=skey_last AND pkey>pkey_last
ORDER BY skey,pkey
LIMIT 100;
until I get an empty response, then I go back to the second method.

if your distribution is such that those skeys that have > 100 records
tend to have a lot more, you might have a higher limit for this case.

All queries are extremely fast with 600 records and it looks like 
the few redundant or empty queries (but very fast) will not be a problem.

What is your opinion about this (apart that it is a bit complex :) ??

looks fine
gnari


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Meaning of ERROR: tuple concurrently updated

[KÖPFERL Robert]

Hi,
I have got some kind of FIFO-queue table.
New records are inserted and the oldest are DELETEd.

Thus I run VACUUM ANALYZE; every night by cron. However I keep getting
ERROR:  tuple concurrently updated
My research lead me to the point that this is VACUUM tothether with the
INSERTs being issued to that table all the time.
But I didn't find a solution to avoid this. AFAIK now the table never gets
VACUUMed at all?

Please, what's really about that, how can it be avoided?


Thanks in advance

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Meaning of ERROR: tuple concurrently updated

[Tom Lane]

=?ISO-8859-1?Q?K=D6PFERL_Robert?= <[EMAIL PROTECTED]> writes:
> Thus I run VACUUM ANALYZE; every night by cron. However I keep getting
> ERROR:  tuple concurrently updated

Have you got other processes also doing VACUUM ANALYZE?  The only known
reason for this to happen is that two processes concurrently ANALYZE the
same table and conflict when they both try to update the same
pg_statistic rows.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Turning column into *sorted* array?

[Felix E. Klee]

Suppose I have a table containing a column with integer values:

CREATE TABLE some_table (x int NOT NULL);
INSERT INTO some_table values(1);
INSERT INTO some_table values(5);
INSERT INTO some_table values(3);

Also, there is a view to that table (probably superfluous here, but it's
in the actual more complicated database design):

CREATE VIEW some_view AS SELECT x FROM some_table;

Now, the goal is to turn the column "x" of the view "some_view" into a
*sorted* array.  I tried the following code.  It works, but is it
reliable?  IOW: is it guaranteed that this gives me indeed a sorted
array?  If not, what's a good alternative?

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

COMMENT ON AGGREGATE array_accum(anyelement) IS
'Found in section "33.9. User-Defined Aggregates" of the PostgreSQL 7.4.2
Documentation.';

SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;

BTW, the best alternative (in terms of execution performance) that comes
into my mind is to create an aggregate that does the sorting right away
while the values "come in" from the rows.  But that'd probably take me
some time to get right.

-- 
Felix E. Klee

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Help with views/rules...

[Peter Bense]

I've got this funky problem.  Basically I have a table that contains:

afl=# \d tblpis_survey_receipt
  Table
"public.tblpis_survey_receipt"
 Column |  Type  | 
Modifiers
++--
 insertion  | integer| not null default
nextval('public.tblpis_survey_receipt_insertion_seq'::text)
 ppt_id | integer| not null
 date_received  | date   | not null
 staff_id   | integer| not null
 survey_type| smallint   | not null
 is_blank   | boolean| not null
 birth_month| smallint   |
 birth_year | smallint   |
 check_ppt  | boolean| not null
 check_dob  | boolean| not null
 check_tracking | boolean| not null
 date_inserted  | timestamp(0) without time zone | not null
 date_modified  | timestamp(0) without time zone | not null

The goal of this table is to provide a location for staff members to
enter information relevant to the tracking of participant surveys. 
They'll have a form where they'll enter this basic data:

ppt_id, date_received, survey_type, is_blank, birth_month, birth_year.

THEN [the part where I'd need the rule thing working] what should
happen is this.

The above 6 fields are entered into a view, which then inserts 'f'
values for the "check" fields by default into the table.

Then [also as part of the rule attached to the view] we perform
checks:

1. check_ppt looks to the participant table to make sure the ppt_id is
valid.  If valid, then:
2. check_dob occurs, which verifies that the month and year of birth
entered by the data entry person matches the respective fields in the
parent record.  Simple enough.
3. check_tracking looks to the tracking table to ensure that there
hasn't already been a record created or field populated for that type of
survey in that participant's tracking record.  If this is okay, then
[and this is the part where things get weird]:

A) We should insert records into the tracking table where there isn't
one already.
B) We should update tracking records where a record exists but there
isn't an entry for that type of survey date received.

Everything works fine until I get to A & B.  If I enter these as
inserts via psql, they work fine.

Problem is, I am using MS-Access, which returns some really weird-ass
error message about the data entered being too large for the field... if
I include more than one `INSERT` in the rule?

Here's my rule [hopefully someone can help advise of a more elegant way
to do this?]

CREATE OR REPLACE RULE tblpis_survey_receipt_in AS
ON INSERT TO vi_tblpis_survey_receipt
DO INSTEAD
(
INSERT INTO tblpis_survey_receipt (ppt_id, date_received, staff_id,
survey_type, is_blank, birth_month, birth_year, check_ppt, check_dob,
check_tracking, date_inserted, date_modified)
VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, 'f', 'f', 'f', now(),
now());

UPDATE tblpis_survey_receipt SET check_ppt='t'
WHERE tblpis_survey_receipt.ppt_id IN
(SELECT ppt_id FROM tblpis_participant);

UPDATE tblpis_survey_receipt SET check_dob='t'
WHERE tblpis_survey_receipt.ppt_id=new.ppt_id
AND tblpis_survey_receipt.check_ppt='t'
AND tblpis_survey_receipt.ppt_id IN
(select ppt_id FROM
tblpis_participant
WHERE
tblpis_survey_receipt.ppt_id=tblpis_participant.ppt_id
AND

tblpis_survey_receipt.birth_month=tblpis_participant.birth_month
AND

tblpis_survey_receipt.birth_year=tblpis_participant.birth_year
);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.survey_type='1' AND
tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking where pre_rc_date IS
NOT NULL);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.survey_type='2' AND
tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking where post_rc_date
IS NOT NULL);

INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
pre_is_blank)
SELECT ppt_id, date_received, staff_id, is_blank
FROM tblpis_survey_receipt
WHERE ppt_id=new.ppt_id
AND survey_type=1
AND check_ppt='t'
AND check_dob='t'
AND check_tracking='t';

INSERT INTO tblpi

Re: [SQL] Changed to: how to solve the get next 100 records problem

[Ragnar Hafstað]

On Wed, 2005-05-18 at 12:42 -0300, Alain wrote:
> I found something that is both fast and simple (program side):
> ...
> subsequent selects are
> (select ... from tab WHERE skey=skey_last AND pkey>pkey_last
>   ORDER BY skey,pkey LIMIT 100)
> UNION
> (select ... from tab WHERE skey>skey_last
>   ORDER BY skey,pkey LIMIT 100)
> ORDER BY skey,pkey LIMIT 100;
> ...
> The only strange thing is that without the 3rd order by, the order is 
> wrong. I didn't expect it because each select is created ordered. Is it 
> expected that UNION mixes it all up? (using postgre 7.4.1)

ORDER BY on subselects are not garanteed by SQL to have any effect,
but Postgres tends to do what you want when possible and not 
detrimental to performance.
In this case, Postgres would probably have kept the order had
you used UNION ALL

a plain UNION implies DISTINCT, which Postgres is free to
implement any way it wants, possibly destroying the order

in this case a UNION ALL is appropriate, as you know that
the 2 selects do not overlap. possibly, a future version
of the planner will be able to detect this.

in any case, the last ORDER BY LIMIT does not cost much, and it
protects you against implementation changes, and limits
the result to 100 records, which might be what you want.

> Please comment on this. I tested  and it worked but I really new to sql 
> and I feel insecure...

it's good.

gnari



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Changed to: how to solve the get next 100 records problem

[PFC]

The only strange thing is that without the 3rd order by, the order is  
wrong. I didn't expect it because each select is created ordered. Is it  
expected that UNION mixes it all up? (using postgre 7.4.1)
	That's because UNION removes duplicates, which it will probably doing  
using a hash (EXPLAIN ANALYZE is your friend).
	Use UNION ALL because your WHERE condition allows no duplicates anyway.  
UNION ALL preserves the order.
	So you can get the LIMIT out of the subqueries and put it around the  
UNION ALL.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Turning column into *sorted* array?

[PFC]

SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
	If you're using integers, you could use the int_array_accum or something  
from the intarray module which is a lot faster.
	I believe intarray also has a function for sorting integer arrays...

BTW, the best alternative (in terms of execution performance) that comes
into my mind is to create an aggregate that does the sorting right away
while the values "come in" from the rows.  But that'd probably take me
some time to get right.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Turning column into *sorted* array?

[Felix E. Klee]

At Wed, 18 May 2005 19:54:08 +0200,
PFC wrote:
> > SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
> 
>   If you're using integers, you could use the int_array_accum or
> something from the intarray module which is a lot faster.  I believe
> intarray also has a function for sorting integer arrays...

No, unfortunately I'm using strings in "real-life" (my example is
perhaps a bit over-simplified).

Let me make my original question a bit more precise: How do I best
transform a column of varchars into a *sorted* array?

-- 
Felix E. Klee

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as

[Chris Browne]

[EMAIL PROTECTED] (Alain) writes:
> Andrew Sullivan escreveu:
>> On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote:
>>
>>>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>>so, we can write the following query:
>> No.  What is the purpose of your query?  You could use ORDER BY and
>> LIMIT..OFFSET to do what you want. I think.
>
> The problem is probably speed. I have done a lot of tests, and when
> OFFSET gets to a few thousands on a multimega-recs database, it gets
> very very slow... Is there any other to work around that?

The other way to do this would involve creating a cursor against the
table, and using suitable FETCHes to grab the portions that you
needed.

In practice, this has seemed to be the relevant answer to what the
application developer actually wanted.

The common "use case" where I see it is in a web application where
they discover that there are 800K records, and the user only wants a
screenful at a time.  

Establishing a cursor, and having the web app jump around on it, seems
to be the right answer.  (Whether it's reasonably implementable by the
developers may be another question, but that's allowed to be a
separate question ;-).)
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as

[Sean Davis]

On May 18, 2005, at 3:52 PM, Chris Browne wrote:
[EMAIL PROTECTED] (Alain) writes:
Andrew Sullivan escreveu:
On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] 
wrote:

Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
so, we can write the following query:
No.  What is the purpose of your query?  You could use ORDER BY and
LIMIT..OFFSET to do what you want. I think.
The problem is probably speed. I have done a lot of tests, and when
OFFSET gets to a few thousands on a multimega-recs database, it gets
very very slow... Is there any other to work around that?
The other way to do this would involve creating a cursor against the
table, and using suitable FETCHes to grab the portions that you
needed.
In practice, this has seemed to be the relevant answer to what the
application developer actually wanted.
The common "use case" where I see it is in a web application where
they discover that there are 800K records, and the user only wants a
screenful at a time.
Establishing a cursor, and having the web app jump around on it, seems
to be the right answer.  (Whether it's reasonably implementable by the
developers may be another question, but that's allowed to be a
separate question ;-).)
In a web app, I doubt that cursors can be useful because of the 
stateless nature of web interaction.  I'd love to hear otherwise, 
but

Sean
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] postgre variable

[bandeng]

hello all,

i want to make dynamic sql query like this

select * from tb_cust where name='erick' and age='20'

to

select * from tb_cust $1

i have tried but error comeup

any suggestion?

thanks
Ricky
-- 
Gutten Aben Sugeng Sonten, Jangane Kurang Santen
bandeng

---(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