Re: [SQL] SEQUENCES
Rodrigo Sakai wrote on 02.10.2006 18:39: Hi all, I need to get all sequences and their respective current values! Is there any catalog table or any other away to get this??? Quote from the manual at: http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html "The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, composite types" Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to autoincrement a primary key...
Richard Broersma Jr wrote on 22.09.2006 21:25:
I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
not sure how to do this. I have read the documentation, and tried "nextval"
as the default - I have searched for the datatype SERIAL, but I am using
navicat and this datatype is not supported. Can someone tell me how to do
this - I just want the integer value for a primary key to autoincrement by
one.
CREATE TABLE bar (idSERIAL PRIMARY KEY);
Is just shorthand notation for:
CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));
Well the shorthand notation has a minor gotcha: you cannot drop the sequence
that has been created automatically. Only if you drop the column itself. Should
not be a problem, but it is a difference between a SERIAL PRIMARY KEY definition
and the "verbose" mode
Thomas
---(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] Updatable views: any decent front-ends?
Bath, David wrote on 24.11.2005 23:57: While I can happily create rules on views to allow inserts, updates and deletes, I can't find a GUI front-end that understands that the view allows record edits that I can run on linux (whether through X or web-based doesn't matter) and simply open the relation and edit data without designing horrible forms with lots of code. I note that MS-Access allows this, as it asks for the field(s) that are unique and can be used for updating when you "attach" a table, but I don't have (or want) a Windows box or MS-Office. So 1) Does anybody know of a tool that allows easy editing of data in views? You might want to try my SQL Workbench: http://www.sql-workbench.net It will not auto-detect the key columns for updating the view, but it will allow you to select them manually if needed (for update/delete). Maybe I'll add automatic detection of the keys in the near future (if I find the time) It is written in Java (Swing) and thus should (and does) work on Linux. Whether it qualifies as "decent" is up to you :) Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT very slow
On 16.06.2005 17:29 PFC wrote: >> If autocommit is on (or fetch size is zero) then the driver will >> build the whole >> result set before returning to the caller. > > > Sure, but that is not your problem : even building the whole result > set should not take longer than a few seconds (I gave you test timings > in a previous message). > So... what ? > What does the taskman say ? CPU at 100% ? how much kernel time ? > Taskmanager (I'm on windows) does not show any high CPU usage. And (as I wrote in the initial post) I cannot see any memory increase in the PG process as well (which I would expect with a result set of that size built up in memory). Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT very slow
On 16.06.2005 16:00 Scott Marlowe wrote: > There's got to be more happening than what this is showing us. A > select, and looping through it, should involve no writes, and therefore > no real performance difference from autocommit versus not. Is there > some underlying trigger on the view or something like that? Some kind > of auditing function? That's exactly the code that produced the mentioned timings. This is - according to the JDBC driver's documentation - the expected behaviour. The driver can be set to use cursor based fetching but *only* if autocommit is false. If autocommit is on (or fetch size is zero) then the driver will build the whole result set before returning to the caller. http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor Thomas ---(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] SELECT very slow
PFC wrote on 15.06.2005 22:04:
It's not the program or Java. The same program takes about 20 seconds
with Firebird and the exactly same data.
Hm, that's still very slow (it should do it in a couple seconds like
my PC does... maybe the problem is common to postgres and firebird ?)
Try eliminating disk IO by writing a set returning function which
returns 100 rows, something simple like just a sequence number and
a text value... if this is slow too... i don't know...
> do you have an antivirus or zonealarm or something ?
Wouldn't that affect all DB access not only PG? And as I said, all other
The 20 seconds are ok. This includes processing of the data in the
application. If I simply loop over the result set and get each column's
value without further processing it takes 4 seconds with Firebird.
Basically I'm doing the following:
rs = stmt.executeQuery("select * from foo");
while (rs.next())
{
for (int i=0; i < 4; i++)
{
Object o = rs.getObject(i+1);
}
}
As I said in my other post, the behaviour/performance in PG is dependent on
the autocommit setting for the connection.
With autocommit set to false the above code takes about 3 seconds in PG
but wit autocommit set to true, PG takes 3 minutes! It seems that it also
is very dependent on the fetchsize (apparently the number of rows that are
cached by the driver). Anything above 100 seems to slow down the overall
process.
Regards
Thomas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SELECT very slow
PFC wrote on 14.06.2005 14:26: [...] Now I fire up python, do a SELECT * from the table and retrieve all the data as native objects... Hm, it takes about 1.3 seconds... on my Pentium-M 1600 laptop... Don't you have a problem somewhere ? Are you sure it's not swapping ? did you check memory ? Are you transferring all this data over the network ? Might an obscure cabling problem have reverted your connection to 10 Mbps ? I'm sure. Everything is running on the same machine, about 350 MB free memory. Ouch. I saw you're on Windows so I tried it on the windows machine there which has a postgres installed, over a 100Mbps network, querying from my linux laptop. The windows machine is a piece of crap, Pentium-II 300 and 256 MB Ram, it takes 7 seconds to retrieve the whole table in a python native object. It's not the program or Java. The same program takes about 20 seconds with Firebird and the exactly same data. Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT very slow
On 09.06.2005 02:06 Tom Lane wrote: > Thomas Kellerer <[EMAIL PROTECTED]> writes: > >>Is there anything I can do, to convince PG to return the first row more >>quickly? > > The solution is to use a cursor and FETCH a reasonably small number of > rows at a time. Thanks for all your answers. I turned out that I did not read the JDBC documentation closely enough (blush) Setting the fetchSize to 1 (or something >0) only uses a cursor if autocommit is disabled (due to the annoying "transaction is aborted" when running DDL scripts that have errors, I usually turn autocommit on). With autocommit off, the rows will be returned "immediately" (so the driver is using a cursor to fetch the data) Regards Thomas ---(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] SELECT very slow
On 09.06.2005 02:06 Tom Lane wrote: > Thomas Kellerer <[EMAIL PROTECTED]> writes: > >>Is there anything I can do, to convince PG to return the first row more >>quickly? > > > libpq's API for PQresult is such that it really doesn't have any choice > but to collect the full result set before it hands you back the > PQresult. I don't know JDBC very well but I think it has similar > semantic constraints. > > The solution is to use a cursor and FETCH a reasonably small number of > rows at a time. > My understanding was that setting the fetchSize() to 1 in JDBC would force the use of a cursor. I'll have to go through the JDBC docs again to check how I can enforce this. Thanks for the answer though Thomas ---(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] SELECT very slow
On 09.06.2005 03:13 Alain wrote: > > > Tom Lane escreveu: > >> Thomas Kellerer <[EMAIL PROTECTED]> writes: >> >>> Is there anything I can do, to convince PG to return the first row >>> more quickly? > > > Are you now looking for the LIMIT ? > > SELECT * FROM table LIMIT 1; > > and when when you wnat the rest of it: > > SELECT * FROM table OFFSET 1; > No I want the whole result. Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SELECT very slow
Hello, I have a table with roughly 100,000 rows (four varchar(100) columns). This is basically test data I generated for something else. I'm using JDBC to access PG (but the behaviour is the same with psql). The problem is, that a SELECT * FROM foobar; takes ages (roughly 3 minutes) to return the first row. I played around with the fetchSize() to disable the result set caching in the Java program first (before I tried psql) but that did not change anything. It seems that PG is actually building up the full result set in the background before delivering the first row. But I cannot see any of the processes (neither my Java process nor the PG processes) using a lot of memory - which I would expect if a result set of that size is created. I need to test a program which should process large result sets (without loading it into memory) and waiting nearly three minutes before it actually starts working is a bit annoying :) A SELECT count(*) FROM foobar is quite fast (about 2 seconds) I hate to say this, but the same SELECT returns the first row more or less instantly with Firebird, SQL Server (MSDE) and HSQLDB. Is there anything I can do, to convince PG to return the first row more quickly? I tried a VACUUM FULL, no change. I increased the shared_buffers to 1000, no improvement either. The execution plan is not really surprising: Seq Scan on foobar (cost=0.00..2510.04 rows=117504 width=63) I'm using PG 8.0.3 on Win2K. Thanks in advance Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query about SQL in PostgreSQL
On 19.04.2005 11:48 Muhammad Nadeem Ashraf wrote: > Hi, > I am new user of PostGreSQL 8.0.1. While using it i faced following > issue. As SQL is Case insensetive Language So the Uper or Lower cases > are not significant. But while using the database there is problem. > If i Create new Table with name (tblstudent) then upon SQL queries it is > fine to use Select * from tblstudent. However i face probel if i give > the name of table in Capital letters i.e. if the name is (tblStudent) > then upon using Select * from tblStudent, following error is appeard: > ERROR: relation "tblst" does not exist > > And if i use the query Select * from "tblStudent" then it works fine. > > This is how SQL is defined in the ANSI standard. If you use double quotes around your object names, then it's case sensitiv. If you omit the double quotes it's not. General rule: do not use double quotes at all, and your fine (this is true for other DBMS like Oracle and Firebird as well) Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
