[SQL] Select Instead on a table
Hello, [I hope this is no FAQ, but I did neither find anything about it in the PostgreSQL FAQ, nor get any hit on RULE or INSTEAD on the mailing list archive search¹...] Is it possible to create a ON SELECT DO INSTEAD rule on a table? All of my tries to do so failed by either complaining that the table is not empty, or converting the empty table into a view, thus making insert/update/delete impossible. What we need is basically a table "outer" where one column is computed via a subselect over another table "inner", something like SELECT id, some, more, rows, ( SELECT aggregate(inner.innerrow) AS collect WHERE inner.id=outer.id) as INNER FROM outer; (In reality, the inner query is a little more complicated because of some weird ordering and limit stuff, but this is the basic idea) Of yourse, we could (and currently do) do this via a View, but this has two disadvantages: - We have an extra relation floating around (outer and outer_view) - We need to create and maintain INSERT/UPDATE/DELETE rules for outer. We cannot change the application code that issues the queries, this is the reason for dealing with rules and views in this case. Thanks, Markus Schaber Footnotes: ¹ The latter was rather surprising to me, may be a defect in the mailing list archive search? -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Select Instead on a table
Hi, Tom, On Thu, 28 Oct 2004 10:22:47 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > > Is it possible to create a ON SELECT DO INSTEAD rule on a table? > > Not unless you want it to become a view. That's what I suspected, after scanning the docs. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] extra info - curious delay on view/where
Hi, Andre, On Thu, 28 Oct 2004 11:53:25 -0300 "andre.toscano" <[EMAIL PROTECTED]> wrote: > Could an INDEX speed up that SELECT? > > > CREATE VIEW "stock_available" as > > > SELECT * FROM stock_details > > > WHERE available = true AND visible = true AND > > > location not in (SELECT descr FROM ignored); Yes, I'm shure. I would try to create (on the underlying table) a conditional index on the column "location" with the condition "available = true AND visible = true". As often, the acutal effect depends on the count of rows the query returns compared to the total rows in the table. And it would be helpful to know the typical queries (especially the rows in the WHERE clauses) to give additional hints on creating indices. A matching index also potentially speeds up ORDER BY queries. HTH, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Update instead rules on Views
Hello, It seems that I have a fundamental misunderstanding how views work. See the following example: --- snip snap --- CREATE TABLE realdata (index int, data varchar, obsolete int); COPY realdata FROM STDIN; 1 hallo \N 2 test\N 3 blubb \N \. -- The obsolete row is a row that is now to be calculated on the -- fly. In our real data, this is to be a nested select, but random() -- works for this example as well. CREATE VIEW testview AS SELECT index, data, (random()*99)::int from realdata; -- But to remain compatibility with old apps, we also need to manage -- updates to the view, which are to be rewritten as follows: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL ; --- snip snap --- But now, when we issue an UPDATE testview SET data='nono' WHERE index=1; we get the result UPDATE 3 So it updated _all_ of the rows instead of the qualified rows (WHERE index=1). SELECT * FROM realdata; index | data | obsolete ---+--+-- 1 | nono | 1 | nono | 1 | nono | But the documentation states: (rules-update.html): | No qualification but INSTEAD | | the query tree from the rule action with the original query | tree's qualification added I read this that the original qualification (WHERE index=1) is applied to the rule, resulting in a transformed query equivalent to: UPDATE realdata SET data='nono' WHERE index=1; which works as expected. Can anyone enlighten me? Thanks, Markus PS: My server version is PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9) -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Update instead rules on Views
Helo, On Tue, 2 Nov 2004 13:05:07 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > -- But to remain compatibility with old apps, we also need to manage > -- updates to the view, which are to be rewritten as follows: > CREATE RULE testview_update_rule > AS ON UPDATE TO testview DO INSTEAD > UPDATE realdata SET > index = NEW.index, > data = NEW.data, > obsolete=NULL > ; I now got it to work with adding a "WHERE index=NEW.index" to the view. Am I correct in my assumption that this means that this only works when I have a primary key (or at least unique) row in my dataset? Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Update instead rules on Views
Hello, On Tue, 2 Nov 2004 16:20:37 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > > -- But to remain compatibility with old apps, we also need to manage > > -- updates to the view, which are to be rewritten as follows: > > CREATE RULE testview_update_rule > > AS ON UPDATE TO testview DO INSTEAD > > UPDATE realdata SET > > index = NEW.index, > > data = NEW.data, > > obsolete=NULL > > ; > > I now got it to work with adding a "WHERE index=NEW.index" to the view. This seems only to work when I update on the INDEX row. I now modified the rule to look like: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL WHERE index = OLD.index; This seems to work now for arbitrary columns, provided that index is an unique row. When I have a table that looks like index | data | obsolete ---+---+-- 2 | test | 3 | blubb | 1 | nono | 3 | hallo | and I issue viewtest=# update testview set data='blob' where data='hallo'; I get: UPDATE 2 and it really updated 2 rows. As far as I understand now, I need a primary key in the underyling table as the qualification from the original statemet is applied to the view results, and not propagated to the underlying table. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] upper/lower for german characters
Hi, Andrei,
On Tue, 9 Nov 2004 16:58:27 +0200
"Andrei Bintintan" <[EMAIL PROTECTED]> wrote:
> Hi to all, I have the problem that:
> select lower('MöBÜEL') or select upper('MöBÜEL') are not working well.
>
> I read on some forums that there is some locale setting that needs to
> be done here, but could not fix this.
>
> I am using the ASCII encoding.
By definition, ASCII does not contain any umlauts.
So I would advise that, first, you switch to an umlaut capable encoding
(e. G. Latin1, Latin9 or UTF-8).
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(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] Unicode problem inserting records - Invalid UNICODE
Hi, David, On Thu, 11 Nov 2004 11:29:22 -0800 "David B" <[EMAIL PROTECTED]> wrote: > show client_encoding gives: > UNICODE So is the data you send also encoded in unicode? Maybe "set client_encoding latin1" or "\encoding latin1" helps. HTH, markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(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] Move table between schemas
Hello, Is there an easy way to move a table to another schema in PostgreSQL 7.4? ALTER TABLE and ALTER SCHEMA don't have this options. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Move table between schemas
Hi, Andrew, On Tue, 16 Nov 2004 06:05:38 -0500 Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > > > ALTER TABLE and ALTER SCHEMA don't have this options. > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > oughta work. This has several drawbacks I can see for now: - For large tables (some Gigs of data), this needs a long time and produces heavy I/O load on the server. - You need twice the disk space until you can delete the old table. - Indices, triggers, sequences and constraints are not transferred. When the target schema is first in the search path of the application, this means that the application works on an incomplete table until I finished the transition.. - It does not automatically convert views or foreign key constraints that point to the table. - The operation is not atomic, thus there may be inserts and updates into the old table that get lost while the "CREATE...SELECT...;DROP TABLE...;" runs. Is there any (possibly ugly, fiddling with system tables) atomic way to move a table between schemas? It should not be much more difficult compared to e. G. renaming a table to implement this, so I couuld not imagine this does not exist until I tried to find out how to do it. Thanks, markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Hide schemas and tables
Hello, Is there any way to hide schemas and relations a user does not have access privileges for? I suspect that the client (in this case, unavoidably excel via OLAP and ODBC) gets this information via querying meta tables, so there is no way to protect foreign schemas and relations from beeing seen. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Split pg_dump script
Hello, The attached dump_split.sh script maybe helpful to some of you. It is called with a database name and a base file name. It first dumps out the schema and metadata of the database in a plain SQL file. After this, it dumps out the data, each table into its own file, compressed with bzip2. This basically allows one to create an ascii dump of a large database and still open and edit the schema with your favourite text editor afterwards. It also allows restoration of single tables (create the table by copying the instructions from the schema file, and then restore the content by piping the data file via bunzip2 into psql). I know that pg_dump -Ft and -Fc also allow to do this, but bzip2 has a far better compression ratio on most data. Comments welcome (I'm subscribed to this list, so no need to Bcc:). HTH, Markus dump_split.sh Description: application/shellscript signature.asc Description: OpenPGP digital signature
Re: [SQL] problem inserting local characters ...
Hi, Garry, Garry schrieb: > Trying to do this insert, I get an error on both the values > "TelefonGeschäft" and "Firmenstraße": > > FEHLER: ungültige Byte-Sequenz für Kodierung »UNICODE«: 0xe165 This sounds as your database was created in unicode (utf-8). > (the 0xe165 differs between the two; the fields in question are regular > type "text" fields) > > Looks like I'm having some trouble with unicode encoding ... The > characters I have are regular 8bit ASCII chars ... How can I fix this? No, 8bit ASCII does not exist. ASCII always is 7 bit. As your error message is in German, I suspect your data is encoded in LATIN1 or LATIN9 (their only difference is the EUR symbol in the latter one). Can you try to add the following command before your insert statements: set client_encoding to latin1; HTH, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [SQL] pg primary key bug?
Hi, Ragnar, Ragnar Hafstað schrieb: >>We are using jdbc (jdbc driver from pg) + jboss (java based >>application server) + connection pool (biult in jboss). >>... >>Will vacuum full generate this problem if we have locked table in this >>time? (It is possible to have locked table in theory) > I do not know if this is relevant, but I have seen jboss applications > keep sessions in 'Idle in transaction' state, apparently with some > locks granted. Would such cases not interfere with vacuum? Most of those "Idle in transaction" problems were caused by suboptimal handling of BEGIN in the pgjdbc driver, this should be fixed in current versions of postgres.jar (build 8.0-310). Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [SQL] Multiples schemas
Hi, Lucas, [EMAIL PROTECTED] schrieb: > Is there a way to construct a multi schema in my data base? > Something like: > mysystem.finances.money.tables > mysystem.finances.money.functions > mysystem.finances.credits.tables > mysystem.finances.credits.functions > mysystem.amount.products.. > AFAIK, there is no way to have hierarchical schemas, but if you only need it for humans, you can name your schemas like finances_money_tables and finances_money_functions. > Or can I use another database like: > createdb DB1 > createdb DB2 > psql DB1 > select * from DB2.schema.table In contrib/, there's a dblink module that lets you select data from other databases, but I'm afraid it is rather limited in usage and performance. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Building a database from a flat file
Hi, Casey, Casey T. Deccio schrieb: > There is more than one problem with this, but the largest is that I > would like to perform this whole database rebuild within one > transaction, so other processes that need to access the database can do > so without noticing the disturbance. However, performing this set of > events (besides populating the temporary table) within a single > transaction takes a long time--over an hour in some cases. > > What are some suggestions to help improve performance with replacing one > set of data in a schema with another? - Create the new date in another schema, and then simply rename those two schemas for "switch over" - Create the new data in differently named tables, and then simply rename all the old and new tables for "switch over". - Have two different set of tables (maybe two identical schemas), and let your application work on a set of views. Then you can change the views via "create or replace view" for switch over. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Building a database from a flat file
Hi, Casey, Casey T. Deccio schrieb: > Question: is there an "easy" way to duplicate an existing schema > (tables, functions, sequences, etc.)--not the data; only the schema? > This way, I would only need to modify one schema (public) to make > changes, and the build schema could be created each time as a duplicate > of the public schema. Maintenance would be much simpler. I do not know about schemas, but for tables you can "CREATE TABLE alpha (LIKE beta)". >>- Create the new data in differently named tables, and then simply >>rename all the old and new tables for "switch over". > This probably would work too, but there may be problems with foreign > keys in renaming the tables one at a time (unless deferrable is used). > To avoid any mess, the previous one works well. AFAIK, the foreign key relations are adopted when a table is renamed, they stick to the same table disregarding name changes. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Building a database from a flat file
Hi, Casey, Casey T. Deccio schrieb: > Building everything in the separate "build" schema works great, but it > is the DELETE (TRUNCATE won't work when foreign keys point to the table) Just as an additional info: You could temporarily drop the foreing key constraints, run TRUNCATE, and recreate the constraints. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres schema comparison.
Hi, Stef, Stef schrieb: > It will be a bonus to pick up exactly what is missing, but for now, just > identifying > differences is what I want to achieve. I'm using postgres 7.3 mostly, but > I may want to use this for 7.4 and 8.0 databases as well. > > Has anybody got some suggestions of what I can do or use to do this. There are (at least) two independently developed pgdiff applications, they can be found at: http://pgdiff.sourceforge.net/ http://gborg.postgresql.org/project/pgdiff/projdisplay.php I did not try the first one, but the latter one worked on some of my datas, but fails on others. I filed a bug report some time ago, but got no answer, so I'm afraid this tool currently is unmaintained: http://gborg.postgresql.org/project/pgdiff/bugs/bugupdate.php?895 But maybe a pg_dump --schema-only on all the databases, and then manually diffing the files may already fulfil your needs. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] [SQL] Postgres schema comparison.
Hi, Stef, Stef schrieb: > The problem I have with this, is that I have to run the command per table, > and seeing that I have over 500 tables in each database, this takes quite a > long time. Some weeks ago, I posted here a script that uses psql to create split dumps. Maybe you can reuse some of its logics to create per-table md5sums for all tables in a database automatically. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org dump_split.sh Description: application/shellscript ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [despammed] [SQL] Crosstab function
Hi, Bandeng, bandeng schrieb: > I have install postgresql-contrib finally... i'm newbie in server. > I use freebsd 4.8, I saw in documentation it is said use tablefunc.sql > but in freebsd i found file tablefunc.so , it is already compiled. is > there suggestion to install tablefunc.so ? I think you misunderstood the relationship between tablefunc.so and tablefunc.sql, as the former one is not the compiled form of the latter one. The .sql file contains the statements that create functions, datatypes, aggregates etc. For functions programmed in C, SQL function definitions eference the .so file and the function symbol name therein so postgresql can load the library and jump into the C code functions, but the SQL files could also define functions by including their source (for languages such as plpgsql). So to "install" the .so file, you have use psql -f tablefunc.sql yourdb. Markus signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andreas, Andreas Joseph Krogh schrieb: >>>So, what you're suggesting is that a restart of the webapp should make >>>vacuum able to delete those dead rows? >>Yes, but that'll only solve your problem for now. You'll have the >>problem again soon. What's keeping open the transaction? > Don't know... All my web-apps semm to have *one* line each in "ps" which says > "dbuser dbname 127.0.0.1 idle in transaction". Those are java-web-apps which > get their connections from a connection-pool(Apache-Commons ConnectionPool), > but there is exactly *one* line pr. web-app which has the "idle in > transaction" line, even tho they have many connections open each. > Any hints on how I can find out what's keeping the connection idle in a > transaction? I realize now that I should probably ask that question on the > pgsql-jdbc-list:-) Could you try the latest postgresql-8.0-311.jdbcX.jar? The current versions should solve the idle in transaction problem, the previous ones tended to issue BEGIN; just after every COMMIT, so there's always an open transaction. Markus signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andrew, Andrew Sullivan schrieb: > Nope. That's a problem with your pool software. It's no doubt > issuing "BEGIN;" as soon as it connects. This problem may as well be caused by some versions of the postgresql jdbc driver, no need to blame the pool software. This is fixed with the current build 311 (and AFAIK with 310, too). signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andreas, Andreas Joseph Krogh schrieb: >>Could you try the latest postgresql-8.0-311.jdbcX.jar? The current >>versions should solve the idle in transaction problem, the previous ones >>tended to issue BEGIN; just after every COMMIT, so there's always an >>open transaction. > I could, but is it save agains a 7.4.5 version? Yes, it should be. I even tested 8.0-310 against a 7.2 server, and the other way round, without any problems. Markus signature.asc Description: OpenPGP digital signature
Re: [SQL] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style
Hi, Cosimo, Cosimo Streppone wrote: > 1) is it possible to know Pg backend uptime with >SQL queries? Or must I look at postmaster.pid file? >or even something else? In contrib, there's a function caled backend_pid() defined in misc_utils.sql, it may be helpful for you. markus signature.asc Description: OpenPGP digital signature
Re: [SQL] Mutex via database
Hi, Enrico, Enrico Weigelt wrote: > i've a dozen of servers processing jobs from a database. > Some servers may only run exactly once, otherwise I'll get jobs > done twice. > > Is there any way for implementing an kind of mutex within the > database, which is automatically released if the holding > process/connection dies ? Use the "LOCK" sql command, possibly together with transaction isolation set to serializable. Markus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] all server processes terminated; reinitializing
Hallo, 2000 Informatica, 2000 Informatica wrote: > Estou usando PostgreSQL 8.0.2 instalado no linux Fedora FC2. > > Esta é a messagem gravada no meu "serverlog": > > WARNING: terminating connection because of crash of another server process > > DETAIL: The postmaster has commanded this server process to roll back > the current transaction and exit, because another server process exited > abnormally and possibly corrupted shared memory. Diese Fehlermeldungen bedeuten, dass ein anderer Prozess des Servers (also eine andere Verbindung) als die, die diese Logmeldungen geschrieben hat, abgestürzt ist. PostgreSQL startet in so einem Fall sicherheitshalber alle Backends neu. Hoffe, das Hilft, Schabi ---(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] plpgsql triggers in rules
Hi, Chester, chester c young wrote: > is is possible for to have a "do instead" trigger on a view that is a > plpgsql function? Kinda. They're called "rules", not "triggers". See http://www.postgresql.org/docs/8.1/interactive/rules.html HTH, Schabi -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] exceptions in rules
Hi, chester, chester c young wrote: > is there any way within a rule to raise an exception? Oh, so you know about rules - why did you ask for them before? You can use a plsql function to raise, if you don't find an easier way. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint)
Hi, Juris, Juris wrote: > Seems, i should `recreate` my db in lowercase.. it will take some time :( Maybe you can modify it "inplace" using ALTER TABLE "OldName" RENAME TO newname; This might even be scripted, select * from pg_tables where schemaname='your schema' gives a list of all tables. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] hi all......................!!
Hi, Akilesh, AKHILESH GUPTA wrote: > i am new to this mailing list. this is my first mail to this group. > i jussst want to confirm that whether is it possible to update a view or > not?? > i think you all help me in solving my queries in future...!! Do you think about issuing UPDATE commands on a view, or do you think about updating the view definition itsself? The former is possible if you add the appropriate 'ON UPDATE DO INSTEAD' Rules to the view, see http://www.postgresql.org/docs/8.1/static/rules.html and http://www.postgresql.org/docs/8.1/static/sql-createrule.html The latter is easily possible if the updated view definition has equal column definitions, just use "CREATE OR UPDATE VIEW ..." instead of "CREATE VIEW ..." to update the view. If your column definitions change, then you'll have to DROP the view before reCREATEing it, maybe it's best to encapsulate this inside a transaction or use a scheduled downtime. Btw, it seems that your '.'-key is broken and chatters. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Mario, Mario Splivalo wrote: > Is it possible to change the transaction level within the procedure? No, currently not, the PostgreSQL "stored procedures" really are "stored functions" that are called inside a query, and thus cannot contain inner transactions. > I'm using Postgres 8.1.2 default isolation level. But, I would like one > stored procedure to execute as in serializable isolation level. I have > created my stored procedure like this: [...] > One thread (thread A) does this: > > 1. java got the message via http (whatever) > 2. java does: begin; > 3. java does: select * from create_message(...) > 4. java does some checking > 5. java does: select * from set_message_status(...) > 6. java does some more checing > 7. java does commit; (under rare circumstances java does rollback). So you even want to change the transaction serialization level within a running transaction? I'm sorry, this will not work, and I cannot think of a sane way to make it work. It is locically not possible to raise the isolation level when the transaction was started with a lower level and thus may already have irreversibly violated the constraits that the higher level wants to guarantee. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Changing the transaction isolation level within the
Hi, Mario, Mario Splivalo wrote: >>you need to set the transaction level after the begin and before every >>other statement... after the begin you have a select that invoke your >>function so that set is not the first statement... > > But I can't do that inside of a function, right? Right, as you need a SELECT to actually execute your function, so the transaction commands inside the function are invoced _after_ the first SELECT began execution. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Mario, My explanation is a little longer, as I think I must at least basically explain some of the fundamentals of database synchronization. Mario Splivalo wrote: >>>Is it possible to change the transaction level within the procedure? >>No, currently not, the PostgreSQL "stored procedures" really are "stored >>functions" that are called inside a query, and thus cannot contain inner >>transactions. > Is above true for the newly introduced stored procedures? (Above, when > mentioning 'stored procedures' I was actualy reffering to 'functions'). I have to admit that I don't know what "newly introduced stored procedures" you're talking about? Is this an 8.2 feature? >>So you even want to change the transaction serialization level within a >>running transaction? I'm sorry, this will not work, and I cannot think >>of a sane way to make it work. > I have some ideas, I just needed confirmation it can't be done this way. > Thank you! :) >>It is locically not possible to raise the isolation level when the >>transaction was started with a lower level and thus may already have >>irreversibly violated the constraits that the higher level wants to >>guarantee. > Yes, a thread will need to start a transaction, I'm just affraid that > create_message could lead me to deadlocks. Don't misinterpret transaction isolation as locking. PostgreSQL (and e. G. Oracle) use a MVCC system for transactions, that doesn't need exclusive locks. Read-only transactions can never collide, and writing transactions only when using transaction isolation "serializable" and manipulating the same data rows. Some of the colliding transactions will be aborted to resolve the conflicts, and the others can commit fine. AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, the only way to introduce deadlocks is to issue LOCK commands to take locks manually. And for this rare case, PostgreSQL contains a deadlock detection routine that will abort one of the insulting transactions, and the others can proceed. I suggest you to read "Chapter 12. Concurrency Control" from the PostgreSLQ docs. Its easy: if you need "read committed" guarantees, then run the entire transaction as "read committed". If you need "serializable", then run the entire transaction as "serializable". If you need real serialization and synchronization of external programs, use LOCK (or take a deep breath, redesign your application and use e. G. LISTEN/NOTIFY. Most times, the usage of LOCK is a good indicator of misdesign.) I just re-read your original posting. You want to make thread B wait until thread A has committed. This will not be possible with the ACID levels. Even when using "serializable" for both threads. If thread B issues SELECT after thread A committed, then all works fine. If thread B issues SELECT before thread A commits, it sees the database in the state it was before thread A started its transaction (so even create_message has not been called). It cannot know whether thread A will COMMIT or ROLLBACK. Transaction isolation is about consistency guarantees, not for true serialization. The reason for this is that databases with high load will need to allow paralellism. So for your case, threas A should issue "NOTIFY" before COMMIT, and then thread B should use LISTEN and then wait for the notification before beginning its transaction. Be shure to read the paragraph about how "NOTIFY interacts with SQL transactions" in the NOTIFY documentation. I don't know the exact sematics of set_message_status and your checks, but it may be another solution to split thread A into two transactions by committing after step 3, and another BEGIN after step 4. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Changing the transaction isolation level within the stored
Hi, Andrew, Andrew Sullivan wrote: >>AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, >>the only way to introduce deadlocks is to issue LOCK commands to take >>locks manually. And for this rare case, PostgreSQL contains a deadlock >>detection routine that will abort one of the insulting transactions, and >>the others can proceed. > > You can too. Consider this: > > t1t2 > > BEGIN;BEGIN; > UPDATE table1 SET col1= UPDATE table2 SET col1= > col1+5; (SELECT col3 FROM > DELETE FROM table2 WHERE table3); > col1 = col1+6;UPDATE table1 SET col1 = >col1 +5; > COMMIT; COMMIT; Hmm, are you shure that this is correct? The delete will always delete 0 rows. http://www.postgresql.org/docs/8.0/static/transaction-iso.html#XACT-SERIALIZABLE contains a nice example in '12.2.2.1. Serializable Isolation versus True Serializability' that you should probably read. > Suppose these are concurrent. The problem here is that each > transaction need something in the other transaction either to > complete or rollback before the work can proceed. So one of them has > to lose. Despite the fact that I don't see such a collision in your example: Depending on the transaction isolation level and exact timings, colliding queries may lead to different results or even one transaction aborted, but there is no deadlock under MVCC. Not needing such locks is the whole point in using MVCC at all. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Does PostgreSQL support job?
Hi, Daniel, Daniel Caune wrote: > I'm not sure to understand. Why calling a function from a script is > different from executing a series of SQL commands? I mean, I can run a > script defined as follows: > > SELECT myjob(); > > where myjob is a stored procedure such as: > > CREATE OR REPLACE FUNCTION myjob() > RETURNS void > AS $$ > > END; > $$ LANGUAGE PLPGSQL; > > Does that make sense? It does make sense if myjob() does more than just execute a bunch of statements, e. G. it contains if(), loops or something else. PLPGSQL is turing complete, plain SQL is not. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Changing the transaction isolation level within the stored
Hi, Andreq, Andrew Sullivan wrote: > I think you don't have a clear idea of what locks are necessary for > updates. Write operations on a row must block other write operations > on the same row. If more than one transaction needs the same kinds > of locks on two different tables, but attempts to get those locks in > the opposite order, you are all but guaranteed a deadlock. MVCC > helps, but it can't avoid locking the same data when that data is > being updated. You're right, I was mislead from my memory. Sorry for the confusion I brought to this issue. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does PostgreSQL support job?
Hi, Daniel, Daniel Caune wrote: >>> I'm not sure to understand. Why calling a function from a script is >>> different from executing a series of SQL commands? [snip] >>>Does that make sense? >>It does make sense if myjob() does more than just execute a bunch of >>statements, e. G. it contains if(), loops or something else. >>PLPGSQL is turing complete, plain SQL is not. > Yes, indeed, that was the idea! There's another reason: For updating the cron job SQL commands, you need root access (or at least shell access) to the database machine. For updating a stored procedure, you need just the appropriate rights in the database. On larger deployments, this can be an important difference. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
H, Achilleus, Achilleus Mantzios wrote: >>PLPGSQL is turing complete, plain SQL is not. > H is SQL equally powerful as a pushdown automaton then??? SQL is _not_ a programming language, it is a query language. It is not meant to be turing complete. Just as e. G. HTML, CSS or RFC2822 are structural or layout languages, but not programming languages. > Just kidding! Now, you're kidding. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] no notnull values, invalid stats?
Hello, The following message occasionally appears in my postgresql log - from temporal corellation, it might be caused by autovacuum. NOTICE: no notnull values, invalid stats Is that anything I should care about? I'm running debianized postgresql 8.1.0-3. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] no notnull values, invalid stats?
Hi, Alvaro, Alvaro Herrera wrote: >>The following message occasionally appears in my postgresql log - from >>temporal corellation, it might be caused by autovacuum. >> >>NOTICE: no notnull values, invalid stats > > I see no such message in 8.1 sources. Is this verbatim or did you > translate it? It is verbatim from /var/log/postgresql/postgresql-8.1-main.log. But I have PostGIS installed in some of the databases, so it might be from there. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Delete with join -- deleting related table entries?
Hi, Bryce, Bryce Nesbitt wrote: > BEGIN; > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > WHERE reservation_id IN > (select reservation_id from reservations where date > magic); > DELETE FROM isuse WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > DELETE FROM reservations WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > COMMIT; > > I suppose I can do the subselect as a perl wrapper, but I was thinking > that maybe SQL could do it all for me Why do you think this won't work? (provided you add the missing ) and ; :-) Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Non Matching Records in Two Tables
Hi, Ken, Ken Hill schrieb: > I need some help with a bit of SQL. I have two tables. I want to find > records in one table that don't match records in another table based on > a common column in the two tables. Both tables have a column named > 'key100'. I was trying something like: > > SELECT count(*) > FROM table1, table2 > WHERE (table1.key100 != table2.key100); > > But the query is very slow and I finally just cancel it. Any help is > very much appreciated. Do you have indices on the key100 columns? Is autovacuum running, or do you do analyze manually? Can you send us the output from "EXPLAIN ANALYZE [your query]"? Btw, I don't think this query will do what you wanted, it basically creates a cross product, that means if your tables look like: schabitest=# select * from table1; key100 | valuea | valueb ++ 1 | foo| bar 2 | blah | blubb 3 | manga | mungo schabitest=# select * from table2; key100 | valuec | valued ++ 1 | monday | euro 2 | sunday | dollar 4 | friday | pounds Then your query will produce something like: schabitest=# select * from table1, table2 WHERE (table1.key100 != table2.key100); key100 | valuea | valueb | key100 | valuec | valued +++++ 1 | foo| bar| 2 | sunday | dollar 1 | foo| bar| 4 | friday | pounds 2 | blah | blubb | 1 | monday | euro 2 | blah | blubb | 4 | friday | pounds 3 | manga | mungo | 1 | monday | euro 3 | manga | mungo | 2 | sunday | dollar 3 | manga | mungo | 4 | friday | pounds I suggest you would like to have all records from table1 that don't have a corresponding record in table2: schabitest=# select * from table1 where table1.key100 not in (select key100 from table2); key100 | valuea | valueb ++ 3 | manga | mungo HTH, Markus ---(end of broadcast)--- TIP 1: 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] Trigger/Sequence headache
Hi, Stephen, Foster, Stephen wrote: > That's what I thought was going to be the answer. I was just hoping I > was making a mistake somehow. It's no big deal but I like things > organized and hate giant holes. > > Ok, one more thing for one of the batch jobs. No problem I have a > cleanup routine. Out of curiosity: Could you explain what's the problem with the holes? Bigserial should provide enough number space that holes are no problem. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Slow update SQL
Hi, Ken, Ken Hill wrote: > Removing the indexes, running the update SQL, and then adding back the > indexes worked much faster. Thank you for you help. It might be a good idea to run VACUUM FULL between updating and reindexing. If you want to CLUSTER on an index, it will be best to create this index first, then CLUSTER the table, and then recreate the other indices. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] VIEWs and TEMP tables problem
Hi, Antal, Antal Attila wrote: > CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code = > AC.code); Here, you bind the View to the permanent table. > CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code; And here you create the temp table that will hide the permanent table. > I read the temporally table definition in the documentation > (http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html) > and I concluded it should be working. I would not think so, as it states "unless they are referenced with schema-qualified names". So the permanent table is not removed, only hidden in the default (non-qualified) name space. Views don't look up the tables by name. Views bind to the table via internal ids, and this binding is strong enough to survive even a table rename, still referencing the same table. > Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem? It is a feature. > This construction came to my mind, because I tried to solve my another > question: > http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad33dea Maybe you try: CREATE VIEW ab_view AS SELECT a.id AS id, a.userid AS userid, a.col AS col_a, b.col AS col_b FROM a LEFT JOIN b ON (a.id = b.a_id AND a.userid=b.userid); EXPLAIN ANALYSE SELECT * FROM ab_view WHERE userid = 23 AND col_a LIKE 's%' ORDER BY col_b LIMIT 10 OFFSET 10; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] group by complications
Hi, Mark, Mark Fenbers schrieb: > Wow! I didn't know you could have a (select ...) as a replacement for a > 'from' table/query. Your SQL worked as-is, except I had to add a 'limit > 1' to the first subquery. > > Thanks! I would have never figured that out on my own! SQL has more power than most think, and PostgreSQL does a good job in implementing most of it. Much more than some other well known "free" databases. Have a look at http://www.postgresql.org/docs/8.1/static/sql-select.html and the other SQL commands at http://www.postgresql.org/docs/8.1/static/sql-commands.html (or your local copy of the manual). HTH, Markus ---(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] passing array(java) to postgre sql function
Hi, Bond,
bond wrote:
> public Object getArray(){
> ArrayList temp = new ArrayList();
> temp.add("");
> temp.add("");
> temp.add("T");
> temp.add("Q");
> return temp.toArray();
> }
> After this i am passing this.getArray() values to database.
As a first guess, have a look at java.sql.Array interface.
And maybe the people on the pgsql-jdbc@postgresql.org mailing list know
better. :-)
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Feature, my misconception or bug??
Hi, Peter, Peter Eisentraut wrote: > Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > >>foodb=# SELECT '255.255.255.255/24'::cidr; >>ERROR: invalid cidr value: "255.255.255.255/24" >>DETAIL: Value has bits set to right of mask. > > >>in this case >>... >>has no bits set to right of >> 8 LSB ^ > > > I'm sure you are aware that "1" is a set bit, so which part are you not > understanding? I guess he's confused by the fact that /24 complains about bits 25-31 beeing set, while /25 does not complain aobut bigs 26-31 beeing set. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Feature, my misconception or bug??
Achilleus Mantzios wrote: > In PgSQL 7.4.12, > > foodb=# SELECT '255.255.255.255/24'::cidr; > ERROR: invalid cidr value: "255.255.255.255/24" > DETAIL: Value has bits set to right of mask. > foodb=# > > SELECT '255.255.255.255/25'::cidr; > cidr > > 255.255.255.255/25 > (1 row) This one is refused in 8.1, so I guess that's a fixed bug. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] CREATE TABLE AS and tablespaces
Hello, Recently, I wanted to create a table from a complex query in a specific tablespace, but CREATE TABLE name AS SELECT ... does not accept a tablespace argument. I worked around it by CREATE TABLE name AS SELECT ... LIMIT 0, then moving the generated table to the other tablespace using ALTER TABLE, and then using INSERT INTO ... SELECT to generate the data into the table. But nevertheless, I'd like to ask here whether there are specific reasons for omitting the tablespace argument from the CREATE TABLE AS statement. If not, I'd like to request this minor feature :-) Maybe it is even possible to further unify CREATE TABLE and CREATE TABLE AS. Thanks, Schabi -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
Hi, Scott, Scott Marlowe wrote: >>But it isn't '-2 months, -1 day'. I think what you are saying is what I >>am saying, that we should make the signs consistent. > Pretty much. It just seems wrong to have different signs in what is > essentially a single unit. > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > again, maybe some folks do. It just seems wrong to me. But we say "quarter to twelve", at least in some areas on this planet. The problem is that months have different lengths. '2 months - 1 day' can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Set generating functions and subqueries
Hello, Today, I stumbled across the following: postgres=# select generate_series(1,2),'othercol'; generate_series | ?column? -+-- 1 | othercol 2 | othercol (2 rows) postgres=# select (select generate_series(1,2)),'othercol'; ERROR: more than one row returned by a subquery used as an expression So it seems that set-returning functions "blow up" the resultset by duplicating rows - so why is this not allowed for subqueries? It is easy to refactor a subquery into a set-returning function, so I think this violates the principle of orthogonality. But there may be subtle reasons of ambiguity here I don't see right now. (I know that usually, a JOIN would be the right thing to do here, but I'm just curious why multi-row subqueries are not allowed.) Btw, having several set-returning functions with equal or different set lengths produce interesting results: postgres=# select generate_series(1,2),generate_series(3,4),'othercol'; generate_series | generate_series | ?column? -+-+-- 1 | 3 | othercol 2 | 4 | othercol (2 rows) postgres=# select generate_series(1,2),generate_series(3,5),'othercol'; generate_series | generate_series | ?column? -+-+-- 1 | 3 | othercol 2 | 4 | othercol 1 | 5 | othercol 2 | 3 | othercol 1 | 4 | othercol 2 | 5 | othercol (6 rows) Is there any way to indicate that I want the cross-product if both set-returning functions have the same length? This could lead to strange effects if the sets have varying lengths otherwhise. (One workaround would be to join two selects, each one having one set-returning function.) Btw, it is not possible to trick PostgreSQL into accepting multi-row selects this way: postgres=# select (select generate_series(1,2)),generate_series(3,4),''; ERROR: more than one row returned by a subquery used as an expression Have fun, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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]
Hi, Klay, Klay Martens wrote: > I am really battling to figure out how to do the same in a postgres > function. http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS could be helpful. HTH Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg reserved words
Hi, Irina, Irina Gadassik wrote: > I am trying to create a table freeze and it seems "freeze" is a reserved > word, but I don't see it in > the list. Also in a different table I cann't create a column freeze. > However it is possible in MySQL and Ingres. It is a PostgreSQL specific reserved word, see http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html However, it is possible to use it quoted: postgres=# create table freeze (foo int); ERROR: syntax error at or near "freeze" at character 14 LINE 1: create table freeze (foo int); ^ postgres=# create table "freeze" (foo int); CREATE TABLE postgres=# select * from "freeze"; foo - (0 rows) postgres=# DROP TABLE "freeze" ; DROP TABLE HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] dbLink Query
Hi, Richard, Richard C wrote: > How do I install or setup the function dbLink so that I can use this > feature. In your PostgreSQL installation, there should be a contrib directory, that contains a file dblink.sql - execute it via psql against your database, and it installs the function definitions. On my debian machine, it is under: /usr/share/postgresql/7.4/contrib/dblink.sql /usr/share/postgresql/8.1/contrib/dblink.sql HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Set generating functions and subqueries
Hi, Tom, Tom Lane wrote: > The behavior of the subquery expression is dictated by the SQL spec: > > 1) If the cardinality of a or a is > greater than 1, then an exception condition is raised: cardinal- > ity violation. That's interesting to know, and it seems to be a clean design. > The fact that the other form is even allowed is more of a holdover from > PostQUEL than something we have consciously decided is a good idea. > (IMHO it's actually a fairly *bad* idea, because it does not work nicely > when there's more than one SRF in the same targetlist.) It'll probably > get phased out someday, if we can find a way to replace the > functionality. I seem to recall speculating that SQL2003's LATERAL > tables might do the job. AFAICS, it can be replaced with JOINs: select * FROM (SELECT 'othercol' AS other) as foo CROSS JOIN (SELECT generate_series(1,2) as a) as fop CROSS JOIN (SELECT generate_series(3,4) as b) AS foq; other | a | b --+---+--- othercol | 1 | 3 othercol | 2 | 3 othercol | 1 | 4 othercol | 2 | 4 (4 rows) > No kidding. I wasn't kidding, I just wanted to point out the different behaviour between equal-length and inequal-length sequences. Thanks, markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] dump with lo
Hi, Marciej, Maciej Piekielniak wrote: > TL> Use 8.1's pg_dump and pg_dumpall to dump from the old server. We fixed > TL> that problem finally. > > I try to copy pg_wrapper,PgCommon.pm to /usr/bin but when i type > pg_dump -V i got: > Argument "." isn't numeric in numeric lt(<) at PgCommon.pm line 439 > Error: You must install at least one > postgresql-client-package pg_wrapper etc. are debian specific and somewhat fragile if you don't strictly obey the debian multi-cluster concept. Using a 8.1 client against a 7.4 server is one of those problematic cases. Install the postgresql-client-8.1 debian package and use /usr/lib/postgresql/8.1/bin/psql directly. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Problem using set-returning functions
Hi,
I'm cross-posting this to the plpgsql list:
We've run into a small problem creating a set-returning function for
PostGIS in PostgreSQL 8.1.0:
CREATE OR REPLACE FUNCTION generate_x (geom geometry)
RETURNS SETOF double precision AS
'DECLARE
index integer;
BEGIN
FOR index IN 1 .. npoints(geom) LOOP
RETURN NEXT X(geometryn(geom,index));
END LOOP;
END
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;
Now, trying to use this function yields the following error:
navteq=# select foo,generate_x(bar) from test;
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "generate_x" line 5 at return next
However, it is fine to call other set returning functions in the same
context:
navteq=# select foo,dump(bar) from test;
foo | dump
-+--
42 | ({1},010100F03F0040)
42 | ({2},01010008401040)
42 | ({3},01010014401840)
23 | ({1},0101001C402040)
(4 rows)
navteq=# select foo,x(geometryn(bar,generate_series(1,npoints(bar
FROM test;
foo | x
-+---
42 | 1
42 | 3
42 | 5
23 | 7
(4 rows)
(This third query is equal to what I expected the failing query to do.)
The table "test" looks as follows;
navteq=# \d test
Table "public.test"
Column | Type | Modifiers
+--+---
foo| integer |
bar| geometry |
navteq=# select foo,asText(bar) from test;
foo | astext
-+-
42 | MULTIPOINT(1 2,3 4,5 6)
23 | MULTIPOINT(7 8)
(2 rows)
I'm shure its a small detail I've blindly ignored, but I'm stuck ATM.
Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] [postgis-users] Problem using set-returning functions
Hi, Regina, Obe, Regina DND\MIS wrote: > generate_series works I think because it is a special case - it isn't > really a set function even though it behaves that way sortof. Well, dump() works as well, and it is no build-in. As a recent thread here stated, it is how set returning functions are expected to work as long as "adjacent tables" are not implemented. http://www.mail-archive.com/pgsql-sql@postgresql.org/msg20545.html Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] functions in WHERE clause
Hi, Steve, [EMAIL PROTECTED] wrote: > SELECT * from some_table WHERE > test_for_equality_is_syntactically_ugly; > The WHERE clause expects the function to return a boolean value. I > can certainly return a boolean value from a function, but here it > seems to me that what the function really has to do is return a > set of boolean values -- the test in the WHERE clause sometimes > evaluates to true and sometimes evaluates to false, and that is in > turn used to constrain the query results. But you can't return a > set of anything (I don't think) in a WHERE clause, because it seems > to want a singular boolean value. And this is as it is intended. The equality test is applied row-by-row, and for each row, it is either true or false, but not undecided. So your query should look like SELECT * FROM some_table WHERE your_function(column_a, column_b); > Is it possible to do what I'm trying to do? I've written a few > simple sql and pl/pgsql functions over the years, but I'm no expert. Yes, it is. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem using set-returning functions
Hi, John, John DeSoi wrote: > With SRFs, you need to specify what you want to select. In other words > if you are calling generate_x(bar) you need "select * from > generate_x(bar)" -- "select generate_x(bar)" will not work. So, then, why does it work with generate_series() and dump()? Confused, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pgsql aggregate: conditional max
Hi, Weimao Ke, Weimao Ke wrote: >> SELECT aid, cat >> FROM tablename AS t >> JOIN (SELECT aid, max(weight) AS weight >> FROM tablename >> GROUP BY aid) AS s USING (aid, weight); >> > This query will return duplicates if there are multiple categories (for > one aid) with the same max weight. Yet, I should be able to remove the > duplicates somehow...:) Try SELECT DISTINCT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Permission to Select
Hi, Eugene, Eugene E. wrote: > This means that some privileges are NOT INDEPENDENT. No, it means that the UPDATE operation needs both UPDATE and SELECT privileges. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] [postgis-users] Problem using set-returning functions
Hi, Regina, Obe, Regina DND\MIS wrote: > Actually I suspect no set returning function created in pgsql will work > the way you are trying to do it. Not sure why. The dump is done in c > and plugged in and I think it explicitly generates a tuple in the target > set for every output. > > Try rewriting your function to something like this and see if it works > > CREATE OR REPLACE FUNCTION generate_x (geom geometry) > RETURNS SETOF double precision AS > ' > SELECT X(geometryn($1,index)) > FROM generate_series(1, npoints($1)) index; > > ' LANGUAGE 'sql' IMMUTABLE STRICT; Yes, it seems to work. This will solve the OPs case. But it is still a good question whether it is possible to accomplish this using plpgsql. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pgsql2shp - Could not create dbf file
Hi, Fay, First, this would be better posted to the PostGIS lists, as pgsql2shp is a PostGIS utility, and not a PostgreSQL SQL query. Fay Du wrote: > The command I used is: pgsql2shp -f newroads gisdb testarea > > Where, newroads is my out put file name, gisdb is database name, > and testarea is table name. > > After hit enter button, I got the message: Initializing... Could > not create dbf file It seems that there was an error creating the newroads.dbf file - each shapefile consists of corresponding .shp, .dbf and .shx files. Do you have permissions on the directory to create those three files? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem using set-returning functions
Hi, Stephan, Stephan Szabo wrote: > It's an implementation detail. Some languages handle SRFs in a way that > can be handled in the select list (SQL and C I think) and others do not > (plpgsql). Ah, that's an enlightening explanation, thanks. > The latter will likely change at some point, although there are > some confusing issues with SRFs in the select list as well, see the > difference in behavior between: > > select generate_series(1,10), generate_series(1,5); > vs > select * from generate_series(1,10) g1, generate_series(1,5) g2; I know that the SRF special semantics are ugly, and would vote for adjacent tables to be implemented as replacement. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Update question
Hi, Fay, Fay Du wrote: > Table cost > > Id edge_idcost > 1 30 101.4 > 2 30 null > 3 40 500.2 > 4 40 null > 545 300.7 > 645 null > I want to set cost value with same edge_id by same value. The existing > values are calculated by calling a function (calculate edge length). I > can calculate the value by same function, but the function was slow. I > wander if I can use update statement to set the second value with same > edge_id. Thanks in advance for your help. It should go like: UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE innr.edge_id = edge_id AND innr.cost is not null) WHERE cost is null; HTH, Marku -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Ugly group by problem
Hello, I have a table filled from third party that basically has the following structure: link_id | feat_id | other | columns... +-+---+--- 1 | 2 | ... 2 | 5 | ... 2 | 23 | ... 3 | 5 | ... 3 | 23 | some | data 3 | 23 | other | data 5 | 23 | ... 9 | 23 | ... This structure is fixed, and we can't change it, but we can create We have about 37 million different link_ids, and 35 million feat_ids. There are feat_ids that appear at several thousand link_ids, but a link_id does not have more than a douzen feat_ids. Now I need to group together all link_ids that have the same set of feat_ids. In the example above, the sets would be (1), (2,3) and (5,9), and the other columns would be run through some aggregate functions. Currently, this is done via an external JAVA application, but I'm looking for a way to express this via sql / plpgsql to ease deployment. I could imagine some ugly code using ARRAY (not tried yet), but how would you pack this problem? It seems that I'm just stuck in my thoughts and miss the beauty way to solve it. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Ugly group by problem
Hi, Achilleus,
Achilleus Mantzios wrote:
> foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as
> foo,_int_union(array(select mt2.feat_id from markustest mt2 where
> mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as
> foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
> foo2 | sum
> +-
> {2}| 1
> {5,23} | 13
> {23} | 14
> (3 rows)
This is much like I intended to do it, but using "select distinct" in
the inner select as I don't have _int_union here, and using a temporary
table to collect the sets of link ids.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] SELECT composite type
Hi, Michael, Michael Burke wrote: > This statement works, but I don't want to duplicate the function call as this > should be unnecessary. Is this for aesthetic reasons (typing the query), or for speed reasons? If the latter one is true, then check that all functions are defined as "immutable" or, at least, "stable". Then the qery planner should flatten them to be called only once. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Hi, Stephan & Eugene, Stephan Szabo wrote: > This is already false AFAICS. Leading or trailing spaces on a string > containing integer get trimmed during the input for example, the string > format of date comes back in a particular but other input formats are > supported. I don't think the above equality is valid for textual > representation. It is even true that the server-internal storage format can be distinct from both the textual and binary representation (aka canonical rep.). This is e. G. how PostGIS handles their geometries. PostGIS geometries have even more representations, available via conversion functions. And for some unicode strings, it even happens that their textual representation is different depending on the client encoding. >>I expect a value of BYTEA to be unchaged too. > I think (as above) that your perception of the problem isn't correct. I agree. The value of the BYTEA is unchanged, it is just a different representation of the BYTEA that allows handling its contents as text, in non-binary safe environments. Imagine having the text representation as simply HEXing the BYTEA contents - it still is an unchanged value. >>why not to define your own unique more_sofisticated representation ? >>(as for bytea is defined.) > AFAICS, there is one, the binary format for integer. Exactly. AFAICS, all built-in data types have both a text and binary representation, as well as most extension types. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Hi, Eugene, Eugene E. wrote: >> Okay, now pass that to strcmp or a %s format. AFAIK, the >> "textual-form" of >> values is meant to be a c-string. "ab\0cd\0" is not a c-string containing >> ab\0cd, it's a c-string containing ab. > WHY strcmp ?! do you really think the user is a fool ? > if the user declared something "binary", he obviously knows what he has > done. But when the user requests the canonical _text_ representation of a byte area data type, why do you consider him declaring it "binary"? > WHY c-string ? the user only wants to get PGresult structure. And he does request the _text_ represenation of the datatypes in this structure. > Since this structure provides a length of each value, you have no need > in c-string. Why do think the user needs it ? A user that does not have a need in C-Strings can fetch the binary representation, getting higher efficency for all datatypes. > "textual-form" is just a name of actually existent convertion rule. > i am not trying to find out a philosophy here. There is no philosophy but orthogonality. There's a textual and a binary form of datatypes. For varchar, byta, int4, float, PostGIS geometries etc... >> I think I don't exactly agree with this description, but I'm unclear >> exactly what you're saying. Are you saying that textual-form is the >> useful representation, or are you saying that textual-form is the >> representation and it is useful? > the actual representasion of most types is pretty useful. The text representation is pretty useful for human readers for _most_ datatypes, the binary representation is much easier to parse for programs. So use the binary representation for everything if you don't want to display the data to the user directly. One could speculate that the textual representation is just a little help for "generic" tools like pg_dump, pgadmin or psql that display data to the user without having any knowledge of the specific datatypes (and without the possibility to have such knowledge). Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Hi, Eugene, Eugene E. wrote: > he did not request this representation. it is _by_default_ He used a function that provided it by default. He could use the other function that allows him to select which representation he wants. > if you wish to provide it by request, please do it. I cannot provide anything, because I'm not a PostgreSQL developer. To be honest, I can provide the user with nice Java Objects, because I'm the maintainer of the PostGIS Java extension, but that's all. >> A user that does not have a need in C-Strings can fetch the binary >> representation, getting higher efficency for all datatypes. > and lose the pretty good representation of all other columns in the same > request. It is not "pretty good". It is human readable, but it are C-Strings with some text. It uses much more CPU power on both sides, Date Formats can be misleading, and PostGIS even reverted to a binary like format for their canonical text representation because the users complained about coordinate drift due to rounding errors in the floating point input/output routines, so pg_dump and restoring the database changed the data. So I cannot see that the textual from is superior for _any_ of the datatypes. IMHO, its sole purpose is to have a generic way to represent the data for generic tools as pg_dump -F p, pgadmin3, psql and such, which cannot know the (possibly user-defined) datatypes in advance. >> There is no philosophy but orthogonality. >> There's a textual and a binary form of datatypes. For varchar, byta, >> int4, float, PostGIS geometries etc... > good. i ask you to slightly change "textual" representation of bytea. This will achieve at least the following: - It will break all libraries, tools and applications that rely on the current behaviour. - It will break the guarantee for generic applications that the text representation of every datatype can be handled as text. - It will break pg_dump -F p (which is the default for pg_dump by the way), thus making it _impossible_ to have "plaintext" dumps of a database, with no easy way of reinventing this behaviour. Those dumps are to be edited with text editors, which don't cope easily with nullbytes and other waste... >> The text representation is pretty useful for human readers for _most_ >> datatypes, the binary representation is much easier to parse for >> programs. > You are right. > but > Who said that i can not display something ? > i thougth, human-readability of some data depends completely on how > CLIENT-SIDE interpret it. > server do not know and should not know > what data is human readable or printable... etc. So what you say is that the canonical text representation should be abandoned completely. Fine. The problem is that all generic applications, that don't know about the concrete datatypes, will get impossible. psql, pg_dump, pgadmin and others. Different from "normal" applications which can have their specific datatypes hardwired in the code or whatever, those applications cannot be taught about how to present the data to an user in a generic way, if there's nothing in the backend. Users and extensions can invent new datatypes as they want, how do you expect the authors of pgadmin or psql to cope with proprietary in-house datatypes of a certain PostgreSQL user? >> So use the binary representation for everything if you don't want to >> display the data to the user directly. > The problem we discuss is not about displaing or printig at all. > Some applications want "textual-form" -- most applications > but not only to display > and in the _same_ query the same applications want bytea... Why do you try so hard to resist understanding the whole point? Those applications _get_ bytea. They just get a Cstring-safe representation of it. It's just like you have to put "quotes" around and \escapes into a string in your program sources if you use any of the weird characters. You have the decision between text and binary format for your query. As libpq is a low level API, it does not abstract you from this difference. You can you use a higher level API that abstracts over the whole issue and gives you nice Objects (like the jdbc library), then you don't have to cope with those representations at all. It also may make sense to provide an extension for lipq that lets you select binary and textual representation column-wise (which might need a protocol extension, I don't have the specs in mind). But it absolutely does not make any sense to break the whole concept of text representations by making it binary for a single datatype. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Joins involving functions
Hi, Jeff Boes, Jeff Boes wrote: > select * from bar > cross join f_foo(bar.b) as foo; > > Unfortunately, this last statement returns an error: > > NOTICE: adding missing FROM-clause entry for table "bar" > ERROR: function expression in FROM may not refer to other relations of > same query level > > Is there a method for joining function calls to tables? The only other > approach I can think of is to write another function that returns "setof". I don't exactly know what you want to achieve, so I'm just wildly guessing. Do you really want the cross join between bar.* and f_foo(bar.b)? If yes, use SELECT x.b, y.b FROM bar x CROSS JOIN bar y; If not, you maybe want SELECT b, f_foo(b) FROM bar; Or it may be you want something like: SELECT * from bar CROSS JOIN (SELECT f_foo(bar.b) from bar) as foo ; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
Hi, Achilleus, Achilleus Mantzios wrote: > Now i am thinking of restructuring the whole architecture as: > - Create one EAR app for every mgmt company > - Create one DB USER for every mgmg company > - Create one SCHEMA (same as the USER) for every mgmt company > (mgmtcompany1,mgmtcompany2,etc...) We're doing a very similar thing here for one of our legacy apps, which luckily does not know anything about schemas, and so the search_path trick does work. However, for most "global" tables we have views with insert/update/ delete rules in the specific schemas, and such shield the application from directly accessing the global data. We even need to mere local and global data this way in some cases. It is ugly, but it works fine and is manageable. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
Hi, Achilleus, Achilleus Mantzios wrote: [schema trickery] > Why do you think its ugly after all? It is not ugly to split such things via schemas per se, but it is ugly to use this schema trick together with a bunch of views, rules and triggers to "upgrade" a legacy single-deployment application into a multi-deployment application with some limited cooperation / data sharing. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Migrating a Database to a new tablespace
Hello, What is the easiest way to migrate a complete database from one tablespace to another? ALTER DATABASE only allows to set the default tablespace, but not migrating the existing database. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Field length ??
Hi, Louise, Louise Catherine wrote: > Could anyone explain, why the field length must be add by 4 : > result 1 : 10 + 4 =14 > result 2 : 5 + 4 = 9 I guess that it is because all variable length datatypes (and text types are such) internally contain a 4 bytes length field. http://www.postgresql.org/docs/8.1/interactive/xtypes.html might be a start if you want to read more on this topic. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Field length ??
Hi, Frank, Frank Bax wrote: > Except that the original fields were neither variable length datatypes, > not type text. > create table test(] > satu char(10), > dua char(5) > ); char is a textual type (in opposite to numeric types), and they are handled as variable length datatypes internally (even if given a limit). See http://www.postgresql.org/docs/8.1/static/datatype-character.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Migrating a Database to a new tablespace
Hello, Markus Schaber wrote: >>What is the easiest way to migrate a complete database from one >>tablespace to another? >> >>ALTER DATABASE only allows to set the default tablespace, but not >>migrating the existing database. > > Is there really no way to do this? Via some scripting, I now migrated all tables and indices, however I found no way to migrate sequences. The problem is that I cannot just move the tablespace itsself via mv/symlink/fiddling_of_systables, because the tablespace contains some other databases that are meant to remain there. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Migrating a Database to a new tablespace
Hello, Markus Schaber wrote: > What is the easiest way to migrate a complete database from one > tablespace to another? > > ALTER DATABASE only allows to set the default tablespace, but not > migrating the existing database. Is there really no way to do this? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Migrating a Database to a new tablespace
Hello, I now pulled the plug, migrated all databases via "create database ... tempate olddatabase tablespace newts" to new tablespaces, one for each database, and dropped all old databases that contained references to the tablespace. Pgadmin3 also shows that the tablespace is not referenced by anything. But I cannot drop it, I get the following message: postgres=# drop TABLESPACE foo; ERROR: tablespace "foo" is not empty It seems that the whole tablespace thing is not yet 100% waterproof, good that this did happen on a developer machine, and not on a production machine. Thanks for your patience, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Migrating a Database to a new tablespace
Hi, Jim, Jim C. Nasby wrote: > That means that the tablespace directory isn't empty. This might be some artifacts from backend kills / crashes, partially during COPY and CREATE DATABASE operations. (It's a developer machine after all). So when pgadmin3 displays no dependencies, can I assume it is safe to empty the tablespace directory manually, and then drop the tablespace? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Migrating a Database to a new tablespace
2224 318554397421 2619 301716 301887 302058 302229 318718397692 Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Migrating a Database to a new tablespace
Hi, Tom, Tom Lane wrote: > Match the subdirectory names against pg_database.oid --- any subdir that > doesn't correspond to any live entry in pg_database is junk and can be > flushed. Within a valid database's subdirectory, match the file names > to that database's pg_class.relfilenode (not oid!) to see if any of them > are live. Ah, that did it. Both "top level" Directories are not found in pg_database.oid, so they are leftovers from dropped databases. I deleted them, and then the tablespace could be dropped. Thanks a lot, Tom. As I said the leftovers are likely to be caused by hard kills and backend crashes, so I would not go into deeper analysis, but maybe the finding and possibly removing of such leftovers should be half-automated to assist server admins. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Migrating a Database to a new tablespace
Hi, Tom, Tom Lane wrote: >>As I said the leftovers are likely to be caused by hard kills and >>backend crashes, so I would not go into deeper analysis, but maybe the >>finding and possibly removing of such leftovers should be half-automated >>to assist server admins. > > It's been discussed. Personally I'm afraid of the idea of automatically > deleting files that seem unreferenced, but having a tool to find them > for manual deletion isn't a bad idea. I also don't like the idea of full automated deletion, that's why I wrote half-automated. I thought of a tool that enumarates suspective files. Then admins can look at the mtime/atime, or move them away and try what happens, or even pipe the output to "xargs rm -rf" if they want. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
Hi, Bruce, Bruce Momjian wrote: > I have work someone did in the past. I just need to be updated to deal > with tablespaces. > > ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > > Let me know if you want details. It looks nice, but I would not like automatic deletion (or only optional or configurable), there might be valid reasons for "alien" files to exist. (README files for admins to communicate what this directory is used for, MacOS ressource forks, Reiser4 special files etc...) What about putting the identification code in a set returning function or view that can be called by the admin. Then tools like nagios can call it and ring the alarm bells when unknown files appear, or possibly autovacuum calls it once on each session to generate log warnings. Btw, are CLUSTER and ALTER TABLE ALTER COLUMN statements that "create or drop a relation" in this sense? And what is with tables getting extended to more than one file? Another idea would be to first create and sync log entries about the files to be created / deleted, and then actually create / delete the files. So on log replay, the backend could watch out for file operations from transactions that never completed. It will slow those operations down, but such file operations are both seldom and relatively expensive on most filesystems. And it does not help in case of fsync=off, but those admins already take worse risks than wasting disk space. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] Slightly confused error message
Hi, I just stumbled over a slightly confused error message: mydb=# select count(*),coverage_area from myschema.streets except select cd as coverage_area from countryref.disks group by streets.coverage_area; ERROR: column "streets.coverage_area" must appear in the GROUP BY clause or be used in an aggregate function As the query looks, streets.coverage_area is actually used in the GROUP BY. I know how to fix the query, but I wonder whether it is worth the effort to try improving the error message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slightly confused error message
Hi, Tom, Tom Lane wrote: >>I just stumbled over a slightly confused error message: > >>mydb=# select count(*),coverage_area from myschema.streets except select >>cd as coverage_area from countryref.disks group by streets.coverage_area; >>ERROR: column "streets.coverage_area" must appear in the GROUP BY >>clause or be used in an aggregate function > >>As the query looks, streets.coverage_area is actually used in the GROUP BY. > > The complaint is 100% accurate; I know that, and won't deny. > the subquery that it's unhappy about is > > select count(*),coverage_area from myschema.streets > > which is an aggregating query, but coverage_area is being used outside > an aggregate without having been grouped by. Yes, and my question is whether it is easy and worth the effort. to add that information (about the acutally offending subquery) to the message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] i am getting error when i am using copy command
Hi, Penchalaiah, Penchalaiah P. wrote: > Copy penchal to ‘/tmp/penchal.out’ > > When I am using this statement I am getting error is : could not open > file ‘/tmp/penchal.out’ for writing: no such file or directory.. It seems that the /tmp directory is missing from your system. This can have two causes, IMHO: You either have a _totally broken_ un*x[1] installation, or you run on Windows or another platform that has different file naming conventions. HTH, Markus [1] un*x is a shortcut for all unixoid operating systems, including BSD, Linux and even MacOS X. -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Compute hash of a table?
Hi, Peter, Peter Manchev wrote: > Is it possible to get the last time(stamp) when the content of a given > table in pgsql has changed??? My solution would be to add a trigger to the table which updates a timestamp in a second table on every insert/update/delete. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] selects on differing subsets of a query
Hi, Ed Temp, [EMAIL PROTECTED] wrote: > First post, be gentle as I have terminology problems and so the > subject might be wrongly worded. Hmm, the biggest mistake seems that "et temp 01" is a very unlikely real name, so you should reconfigure your mail client :-) > What I am trying to construct is a *single* query showing the total > number of males in the table > and also the total number of male vegetarians in the table, i.e. the > 2nd value is computed on a subset of the records needed for the first > value. > > As 2 queries this would be: > select count(*) from mytab where gender='m' > select count(*) from mytab where gender='m' and diet_pref='veg' Have you tried to UNION ALL the two queries? > The table is big and I'd like to do the select where gender='m' only > once. (In the actual situation the select is on a date range) SELECT count(*),diet_pref='veg' FROM table WHERE gender='m' GROUP BY diet_pref='veg' Is not exactly what you want, as your application still has to add two numbers to get the total result, but avoids the duplicated table scan. SELECT count(*),count(nullif(diet_pref='veg', f)) FROM table WHERE gender='m' Should also give you both counts, this time in different columns, also avoiding the duplicated table scan. It relies on the fact that count(something) is only called if something is not null, whereas count(*) is called for every row (as a special case). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selects on differing subsets of a query
Hi, Ed, [EMAIL PROTECTED] wrote: > It's actually a temporary mailbox just in case the list attracts spam :-) As far as I can see, the PostgreSQL lists are extremely low on spam attraction, compared to other lists. I think this is a result of the "only subscribers may post" policy, but I'm getting of topic. > Thank you for your help, I will study it when I get development time > on the database. Tell us about your findings here. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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] selects on differing subsets of a query
Hi, Bruno, Bruno Wolff III wrote: >>I think this is a result of the "only subscribers may post" policy, but >>I'm getting of topic. > > Note the policy is really only subscribers may post without moderator > approval. Thanks for clarification. I also think that the moderators are aided by some automatic spam classification techque. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How can I read display message from a C function
Hi, Fay, Fay Du wrote: > I would like to put some message in my C function ( myTestFunction). > Currently, I want to see time for each function call inside > myTestFunction. The output to the screen commands are in myTestFunction. > myTestFunction is called from postgresql. How can I see the messages? Hmm, would statement logging help you? If not, you could use elog(NOTICE, "message with time"); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Most efficient way to hard-sort records
Hi, Miroslav, Miroslav Šulc schrieb: > I have a table with cca 100,000 records. I need to hard-sort the records > by a key from second table. By hard-sorting I mean storing position of > each row in the main table. Here is what my tables look like: > > main_table: id, name, position > key_table: id, main_table_id, key, value > > Here is how I need to sort the records: > SELECT * FROM main_table > INNER JOIN key_table ON main_table.id = key_table.main_table_id > WHERE key = 'param' > ORDER BY value > > I currently collect all ids from main_table in sorted order and then > update the position field for each row in the main_table one-by-one. Is > there a better/faster/more efficient solution? Create an SQL function that selects the sort value from the key table when given id as parameter, and then create a functional index on the table, and CLUSTER the table on the index. Scratch-Code (untested): CREATE FUNCTION getvalue (ID int4) RETURNS int4 AS " SELECT value FROM key_table WHERE value=$1 LIMIT 1" LANGUAGE SQL STRICT; CREATE INDEX main_table_order_idx ON main_table (getvalue(id)); CLUSTER main_table_order_idx ON main_table; HTH, Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Returning String as Integer
Hi, Ben, Ben K. schrieb: > I tried int8(id) but java didn't take it as Integer. (It took int8 as > something like Long.) Yes, and that's good, as PostgreSQL int8 and java long actually are the same datatype (64-bit signed two's-complement). PostgreSQL int4 and Java int are the same (32-bit), as well as int2 and short. HTH, Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Multi-column index not used, new flipped column index is
Hi, Otis, [EMAIL PROTECTED] wrote: > I'm using PG 8.0.3 and recently spotted a query that was not using a > multi-column index I had created. The index looks like: > > CREATE INDEX . ON FooTable(fkColumnOne, fkColumnTwo); > > The query that was not using the index was using: > > SELECT a bunch of columns and joins WHERE > FooTable.fkColumnTwo=1000; That's how it is supposed to be currently. AFAIK, Oracle has a technique called "index skip scan" that could take (limited) use of that second index, but PostgreSQL currently does not support them. > So I added another index where the indexed columns are flipped: > > CREATE INDEX . ON FooTable(fkColumnTwo, fkColumnOne); > > Now the query started to use that index -- good! But now I have 2 > indices that are nearly the same, and that means overhead during > INSERTs/DELETEs. :( > Is there a way to get this to use that first index, so I don't have > to have this additional index? If I had PG 8.1.3, would PG know how > to use that first index? I seem to recall something about this > somewhere... but I don't see it at > http://www.postgresql.org/docs/whatsnew . Bitmap Index Scans can be your solution, but AFAIK they were invented in 8.1. For bitmap index scans, you have one index on fkColumnOne and one on fkColumnTwo, and the query planner knows to combine them when both columns are given in a WHERE clause. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with a seq scan on multi-million row table
Hi, Otis, [EMAIL PROTECTED] wrote: > I'm not sure which numbers you are referring to when you said the estimate is > off, but here are some numbers: > The whole table has 6-7 M rows. > That query matches about 2500 rows. > > If there are other things I can play with and help narrow this down, please > let me know. Did you try to set higher statistics targets for this columns? For experimenting, I'd try to set it to 100 or even higher, then ANALYZE the table, and then retest the query. HTH, Marks -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
