[SQL] Using Transaction Blocks w/ SELECT

[postgres]

Goinging throught the libpq docs, I noticed that in all of the examples involving
select statements, transaction blocks are used.  I see why this is necessary for
write operations, but I don't see the need in read operations that don't commit.  Am
I missing something?  Any help appreciated.


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

[SQL] switching default integer datatype to int8 and "IN (...)" clause

[postgres]

 Hi,

 Short: postrgesql-7.2.3 doesn't figure to convert int4 to int8 
implicitly / by context (as in a query). How do I help it ?

 Long:
  I have index on a table, a select like this takes a split second:
# select * from file where id = fileIDseq.last_value-1;
   id   | name  
   -+
1921777 |  icons 

 but a select like this takes ages (long time):
# select * from file where id = 1921773;
   id   | name  
   -+
1921777 |  icons 

 but a select like this is quick again:
# select * from file where id = int8(1921773);
   id   | name  
   -+
1921777 |  icons 

 the secret seems to be that 
# explain select * from file where id = fileIDseq.last_value-1;
Nested Loop  (cost=0.00..6.04 rows=1 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Index Scan using file_pkey on file  (cost=0.00..5.02 rows=1 width=1351)

 whereas

# explain select * from file where id = 1921773;
Seq Scan on file  (cost=0.00..58905.95 rows=1 width=1351)

 The reason seems to be that fileIDseq.last_value-1 is type bigint
 (int8), whereas "1921773" is of type integer (int4).

 Now 
# explain select * from file where id in 
(fileIDseq.last_value-1,fileIDseq.last_value-1);
Nested Loop  (cost=0.00..6.04 rows=1 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Index Scan using file_pkey on file  (cost=0.00..5.02 rows=1 width=1351)
 BUT
# explain select * from file where id in 
(fileIDseq.last_value-1,fileIDseq.last_value-333); -- "-333" instead of same "-1"
Nested Loop  (cost=0.00..92278.69 rows=2 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Seq Scan on file  (cost=0.00..54138.56 rows=1906956 width=1351)


 Why ?

 Also, how do I tell postgresql that it should by default interpret
integers as "int8"s, and not as "int4"s ? (So that I don't have to
keep saying "int8(XYZ)" as in "select * from file where id = int8(1);"


Thanks,

  John






mydb# \d file
 Table "file"
  Column  |Type |  Modifiers
--+-+-
 id   | bigint  | not null default nextval('fileIDseq'::text)
 name | character varying(255)  |
Primary key: file_pkey

mydb=# \d file_pkey
Index "file_pkey"
 Column |  Type
+
 id | bigint
unique btree (primary key)

mydb==# \d fileidseq
  Sequence "fileidseq"
Column |  Type
---+-
 sequence_name | name
 last_value| bigint
 increment_by  | bigint
 max_value | bigint
 min_value | bigint
 cache_value   | bigint
 log_cnt   | bigint
 is_cycled | boolean
 is_called | boolean


-- 
-- Gospel of Jesus' kingdom = saving power of God for all who believe --
 ## To some, nothing is impossible. ##
   http://Honza.Vicherek.com/



---(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] switching default integer datatype to int8 and "IN (...)"

[postgres]

 Thanks Andy - this "solves" the problem(*1) on the first level, where I 
know how to quote the params, so they must be evaluated / casted.

 But when I get into the subselects, I don't know how to make postgres to
cast / evaluate the results of the subselect, so it again does only
sequential scan.

 How do I make postgres cast ( or evaluate? ) the subselect ?, so that 
when I do

 # explain _the_right_select_quesry_with_subselect_

 I want to get (Index Scan):
Index Scan using file_pkey on file  (cost=0.00..5.01 rows=1 width=8)
  SubPlan
->  Materialize  (cost=37209.28..37209.28 rows=9535 width=8)
  ->  Index Scan using parentid_name_idx on file  (cost=0.00..37209.28 
rows=9535 width=8)

 but now instead I'm getting with this:
 # explain select id from file where id in( select id from file where parentid 
='355764');

 I don't want to get (Seq Scan): (that's what I'm getting now with the above query)
Seq Scan on file  (cost=0.00..70956514802.83 rows=953478 width=8)
  SubPlan
->  Materialize  (cost=37209.28..37209.28 rows=9535 width=8)
  ->  Index Scan using parentid_name_idx on file  (cost=0.00..37209.28 
rows=9535 width=8)


 What's the right _the_right_select_quesry_with_subselect_ with possibly
several nested subselects ?

Thanks,

   John

(*1) PS: I guess the problem is that somehow postgres doesn't know by 
default that it should try to "cast" the results of the subselects into 
type that it is to be comparing it with. (which is int8). Is there a way 
to formulate the query to ask for the cast, perhaps explicitly ? Or is 
there a way to set a variable or some other condition which will tell 
postgres to perform this cast implicitly ? -- Thanx !


On Thu, 9 Jan 2003, Andrew J. Kopciuch wrote:

> >  but a select like this takes ages (long time):
> > # select * from file where id = 1921773;
> >id   | name
> >-+
> > 1921777 |  icons
> >
> 
> I believe the reason is this : the numeric literal is first considered an int4 
> becuase it falls within the range of int4 (-2147483648 to +2147483647).
> 
> try quoting the literal like this:
> 
>  # select * from file where id = '1921773';
> 
> This forces the literal to be evaluated.  If you do an explain on that query 
> ... you should see that the query planner uses the index as expected and that 
> the condition used on the index is using the literal value cast to a big int.
> 
> 
> That's just my understanding anyway.
> 
> 
> Andy


-- 
-- Gospel of Jesus' kingdom = saving power of God for all who believe --
 ## To some, nothing is impossible. ##
   http://Honza.Vicherek.com/


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

http://archives.postgresql.org

Re: [SQL] update more than 1 table (mysql to postgres)

[postgres]

On Fri, 30 Jan 2004, treeml wrote:

> I am migrating from MySQL to Postagres. I have problem with postgres
> updating 2 tables with one statement.
> 
> In MySQL I can update 2 tables (parent, child) with a statement like this
> 
> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET
> parent.field1 = 'company',
> child.field2 = 'john'
>  WHERE child.pid = 7
> 
> Or I can also do
> UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john'
> WHERE
> parent.pid = child.foreign_key
> AND child.pid = 7
> 
> 
> But I couldn't do that in Postgres,
> Only one table is allowed in an update statement. I tried to create a view,
> and updating the view,  but that was not allowed.   I could do 2 SQL
> updates, but I am sure there is a better way to do this.  Anyone have any
> idea. Appreciated.

You can use a transaction:

begin;
update parent set ...;
update child set ...;
commit;

Or if you want to use a rule, you can define a rule to do it:

create or replace rule my_view_update_rule as
on update to my_view do instead (
...

-j

-- 
Jamie Lawrence[EMAIL PROTECTED]
"Perhaps the truth is less interesting than the facts?" 
   - Amy Weiss, Senior Vice President of Communications, RIAA



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Problems with tsearch2: ERROR: datumGetSize: Invalid typLen 0

[postgres]

Hi all -

I'm playing with tsearch2. It built and installed normally (this is PG 7.3.2 
on an Alpha running Debian Stable, with the December 18th tsearch2), and 
portions of it work, but, for instance ts_tsvector doesn't:

jal=# select to_tsvector('default', 'Our first string used today first 
string');
ERROR:  datumGetSize: Invalid typLen 0

Archive searches didn't seem to turn anything up... Has anyone seen this
before?

-j

-- 
Jamie Lawrence[EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in 
the marketplace.
   - Philip Greenspun



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

Re: [SQL] Aggregate Functions Template

[postgres]

Hi, Sqlers -

I just installed 8.0.3, and am getting up to speed with the new
features.  First on my list is Tablespaces. In the docs, I see this:


Note:  There is usually not much point in making more than one
tablespace per logical file system, since you cannot control the
location of individual files within a logical file system. However,
PostgreSQL does not enforce any such limitation, and indeed it is
not directly aware of the file system boundaries on your system. It
just stores files in the directories you tell it to use. 

(http://www.postgresql.org/docs/8.0/interactive/manage-ag-tablespaces.html)

I assume this is from the perspective of performance, correct? I was
planning on doing a tablespace per logical project, as we internally
structure most other things that way, and also to ease moving things
around in the event we restructure filesystems, move things between
servers, etc.

In general, at least on our development machines, I was planning on
using them to make data management easier.  I note that I'm not seeing
any Oracle style alter tablespace ... commands for moving things around,
but it appears from that page that it is possible to do by changing the
$PGDATA/pg_tblspc/$symlink and updating pg_tablespace (even if doing so
is 'not recommended'). 

Is this a flawed use of tablespaces?

Thanks,

-j


-- 
Jamie Lawrence[EMAIL PROTECTED]
"Reality must take precedence over public relations, for nature cannot be
fooled."
   - Richard P. Feynman



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Table design question

[postgres]

On Thu, 01 Jun 2006, David Clarke wrote:

> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the

The use of surrogate keys is a mostly religious issue. Celko is an
Orthodox, many others are Reform. Where you want to align yourself
is a personal choice. 

In defense of the Reform movement, I'd note that modern DBs are more
performant with ints than varchars. More importantly, I've found it
much easier to modify DBs designed with surrogate keys than natural
keys, especially when natural keys span columns. It allows a rather
simple convention for coders to write against, and avoids some messy
modification issues when the spec changes.

It is not my intention to bash the purists, and there are good arguments
on the Ortho side, too. I'm merely giving a bit of advice from the
point of view of someone who lives in the constant evolution side
of DB usage.

-j

-- 
Jamie Lawrence[EMAIL PROTECTED]
When I was a boy I was told that anybody could become President. 
Now I'm beginning to believe it.
   - Clarence Darrow



---(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: [SQL] Table design question

[postgres]

On Thu, 01 Jun 2006, Chris Browne wrote:

> Celko is decidedly *NOT* promoting the notion that you should use a
> 100 byte long "natural key."
> 
> Jamie's comments of "Orthodox versus Reform" seem reasonably
> appropriate in outlining something of the difference between the
> positions.  

Just to be clear, that was all I was trying to do. I probably should
have mentioned that any attempt to use such an attribute as a PK should 
be met with a baseball bat or other shillelagh-ish implement, but was 
interrupted several times during that email drafting.

> I may not care for doing this; you may not either; a company that
> builds auto parts that they want to sell into the automotive industry
> may care about standardizing their part IDs quite a lot.

This is another important point. In some situations, a rigid data model
can be a godsend to coders. If you happen to sit in such an enviable
position, I would encourage you to take advantage of it. (This doesn't
mean picking bad keys, of course.) 

I liberally sprinkle surrogate keys around simply because most of the
projects I work on have transient requirements, so spontaneous rejiggery 
and various pokery are both commonplace, and SKs provide "enough" data
integrity that the cost/benefit curve seems to peak there. Were I doing
projects that had longer release cycles, I'd re-evaluate that position,
and likely see a marginal reduction in bugs.

None of this should be taken as bashing Celko - he's a smart man and an
excellent source of advice.

-j

-- 
Jamie Lawrence[EMAIL PROTECTED]
When I talked to the president, he was loaded.
  - Brent Scowcroft, Kissinger's assistant, 10/11/73



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

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

[SQL] oracle varray functionality?

[Postgres User]

I've run across a custom type in an oracle database that I am porting to 
PostGreSQL: 

create or replace type number_varray as varray(1000) of number; 

Is the int4array example the same as this? 

create type int4array(input=int4array_in,output=int4array_out,
internallength=variable,element=int4); 

pgu

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] varray? table of varchar?

[Postgres User]

Hi, 

I posted asking about varray a few days ago. Also I've run into an Oracle
type that is defined as "table of varchar2(4000) index by binary_integer". 

Has anyone encountered either of these before? I'm not exactly sure what 
they do as I'm not an Oracle expert. So it is hard for me to see what these
should be in PostgreSQL. 

D

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] Which type of functions are best?

[Postgres User]

Hi,
I'm just starting out and am looking to speed up queries using either 
SQL functions or PLPGSQL functions.  I have googled around and have not 
found a great answer saying that this is the way to go.  I would like to 
use PREPARE/EXECUTE... but of course they only last for each connection, 
I would like something more permanent.

Thanks for your input,
J
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] ERROR: unterminated quoted string... help

[Postgres Admin]

Hi
I'm trying to insert encrypted data into the database and I'm noticing 
error dealing with quotes. Below is the error print out...

suggestions and/or at least point me in the direction to find a solution,
Thanks,
J

INSERT INTO sample.users (user_name, first_name) VALUES 
('jokers', '=ïµiF!¶6(ÖŸã?¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')

Warning: pg_query() [function.pg-query]: Query failed: ERROR: 
unterminated quoted string at or near "'=ïµi" at character 68 in 
/usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] ERROR: unterminated quoted string... help

[Postgres Admin]

Scott Marlowe wrote:
Use a bytea field and use pg_escape_bytea() to prepare the data for
insertion.
 

Thanks Scott, I will try it now.
J
---(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

[SQL] PostgreSQL and Delphi 6

[Postgres Admin]

I have a client who wants to use Delphi as a front end to a Database, I
would like to use PostgreSQL over MSSQL and have been looking at the
psqlodbc project.  Will psqlodbc connect with Delphi 6? Basically, I'm
wondering if anyone has experience with it?   Any help will be appreciated.

Thanks,
J


---(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] PostgreSQL and Delphi 6

[Postgres Admin]

So you installed psqlodbc 8 on the client machine with Delphi installed,
correct?  What problems did you have with cursors?  Any other suggestions?

Thanks a lot for the help!
J

Din Adrian wrote:
> we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is
> the  server side cursor = doesn't work properly ... so we are using
> client side  for datasets  :)
>
>


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Data insert

[Postgres Admin]

Sorry for the attachment, but copying and pasting this data does not work.

I don't have any idea how to insert the type of data into PostgreSQL. 
Basically, it's encrypted data in which I would like that keep raw format.

Thanks for any help,
J


sample_data.pdf
Description: Adobe PDF document

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

[SQL] plpgsql question

[Postgres Admin]

Can I do something like this:

CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER);
INSERT INTO sample(node,parent) VALUES(1,0);
INSERT INTO sample(node,parent) VALUES(2,0);
INSERT INTO sample(node,parent) VALUES(3,1);
INSERT INTO sample(node,parent) VALUES(4,3)

CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement)
RETURNS SETOF samle AS $$
DECLARE
articleRow sample%ROWTYPE;
BEGIN
FOR articleRow IN SELECT comments
FROM theirry.articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT articleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

Thanks,
J

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

   http://archives.postgresql.org

Re: [SQL] [ADMIN] plpgsql question

[Postgres Admin]

I have data in one table called articles and I would like to make a
function in which takes certain data from it and display the results.

Example:

CREATE TABLE articles (
article_id serial,
title varchar(200),
posted timestamp,
article_subject varchar(200),
article_body text,
allow_comments boolean,
comments smallint
);

I understand one way to display a results I would like is creating a
TYPE with the columns needed.

CREATE TYPE articles_output AS (
article_id int
title varchar(200),
article_body text,
comments smallint
);

Now I would like the function to display data using the LIMIT and OFFSET
option
ex: SELECT title, article_body, comments FROM articles ORDER BY
article_id DESC *LIMIT 4 OFFSET 0*;

this is function I created:

CREATE OR REPLACE FUNCTION article_display(integer, integer)
RETURNS SETOF article_output AS $$
DECLARE
articleRow article_output%ROWTYPE;
sampleRow RECORD;
BEGIN
FOR sampleRow IN SELECT title, article_body, comments
FROM articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT sampleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

this is the error ->
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "article_sample" line 10 at return next

Can I do this or are there better options?

Thanks for the help,
J

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