[SQL] Inherited tables: How stable is that feature?
Is it likely that table inheritance is going to be removed in future PostgreSQL versions (or that the semantics change radically)? Or can I built a database on top of this feature without running the risk of a major restructuring task in the next few years? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Determining if two subnets intersect
Is there some efficient PostgreSQL expression which is true if and only if two subnets (given as values of type cidr) have non-empty intersection (even if the intersection is not a CIDR network)? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Determining if two subnets intersect
Tom Lane <[EMAIL PROTECTED]> writes: > Florian Weimer <[EMAIL PROTECTED]> writes: > > Is there some efficient PostgreSQL expression which is true if and > > only if two subnets (given as values of type cidr) have non-empty > > intersection (even if the intersection is not a CIDR network)? > > Maybe I'm missing something, but ISTM it's only possible for two > CIDR subnets to overlap if one contains the other. So you could > check with > > A <<= B OR B <<= A Oh, I think you are right; I haven't paid attention. Thanks. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Calling stored procedures in table constraint checks
I guess I need an example how I can pass an entire row to a stored procedure called in a table constraint check. Is this possible at all? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Index to support LIKE '%suffix' queries
Is it possible to create an index to support queries of the form "column LIKE '%suffix'" (similar to an ordinary index for LIKE 'prefix%', which I also need)? I could define a function which reverts strings (or revert them in the application) and use a normal B-tree index, but I wonder if there is a better way. ---(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] Index to support LIKE '%suffix' queries
* Tom Lane: > Florian Weimer <[EMAIL PROTECTED]> writes: >> Is it possible to create an index to support queries of the form >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> 'prefix%', which I also need)? > > Sounds like what you *really* need is full-text search, not half > measures ... have you looked at tsearch2? Uh-oh, the table in question has got 50+ million rows (and is still growing). Each "document" contains about three words. Do you think tsearch2 could deal with that? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Index to support LIKE '%suffix' queries
* Alvaro Herrera: >> > Florian Weimer <[EMAIL PROTECTED]> writes: >> >> Is it possible to create an index to support queries of the form >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> >> 'prefix%', which I also need)? > > It is possible to create a functional index on the reverse of the > string. Okay. Is there a predefined reverse function? I couldn't find one and I'm wondering if I just missed it. > Whether or not this beats tsearch2 is something you should investigate ... It's also possible that for this type of query, sequential scans are good enough. I forgot that they are quite fast. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT DISTINCT too slow
* Alvaro Herrera: > Miroslav ?ulc wrote: >> The GROUP BY is really fast :-) > > Doh! How does it do it? It uses a hash table and can therefore discard duplicate rows more quickly (essentially linear time in the number of rows if the number of different rows is bounded). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] INSERT/UPDATEs cycles and lack of phantom locking
I've got several tables where I need to either insert new records, or update existing ones (identified based on the primary key). For performance reasons, I want to do this in batches, so I plan to use something like this: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- SERIALIZABLE ... CREATE TEMPORARY TABLE tmp (key TEXT NOT NULL, new_val INTEGER NOT NULL); COPY tmp (key, new_val) FROM STDIN; ... \. -- SAVEPOINT tmp_created; -- (see below) CREATE TEMPORARY TABLE tmp2 AS SELECT tmp.key, new_val, real.val AS old_val FROM tmp LEFT OUTER JOIN real ON tmp.key = real.key; UPDATE real SET val = new_val + old_val FROM tmp2 WHERE old_val IS NOT NULL AND tmp2.key = real.key; INSERT INTO real SELECT key, new_val FROM tmp2 WHERE old_val IS NULL; If this is run concurrently, the INSERT may fail. In this case, I rerun the transaction. Actually, I want to rollback to the tmp_created checkpoint, but I don't think this will pick up the new rows in the "real" table, and the INSERT will fail again. Usually, the batch size is small enough that the necessary data is still cached, and concurrent updates aren't the norm, so this approach (complete transaction rollback) is not completely infeasible. However, I still wonder if there is a more straightforward solution. Serializing the updates isn't one, I think. Is there some form of table-based advisory locking which I could use? This way, I wouldn't lock out ordinary readers (which is crucial), but the reading part of an updating transaction would be blocked. For bonus points, deadlocks would be automatically detected by PostgreSQL (although I would order the locks properly in the usual case, but I can't guarantee this for all codepaths due to the modularity of the application). Florian -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(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] INSERT/UPDATEs cycles and lack of phantom locking
* Tom Lane: > Why do you think that? If you're running in READ COMMITTED mode then > each statement takes a new snapshot. Ah, I should have explained that. I might need the SERIALIZABLE isolation level in the future (this code doesn't need it, but other things in the same transaction might require it). In addition, it occurred to me that I get the INSERT failure only if there is a suitable PRIMARY KEY/UNIQUE constraint on the table. I haven't got that in all cases, so I need that advisory locking anyway, I fear. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking
* Florian Weimer: > In addition, it occurred to me that I get the INSERT failure only if > there is a suitable PRIMARY KEY/UNIQUE constraint on the table. I > haven't got that in all cases, so I need that advisory locking anyway, It seems that LOCK TABLE ... IN EXCLUSIVE MODE does exactly what I need: it locks out itself (and write access), but not read access to the table. And deadlocks are detected as well. Yay! -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(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] Rows with exclusive lock
* Martin Marques: >> That's what SELECT FOR UPDATE does. > > Hi Alvaro, > > After the SELECT FOR UPDATE other transactions can still see the > locked rows. I want a read/write lock, so no one can access does rows. You should probably run the other transactions at SERIALIZABLE level. I suppose this will make them wait for the completion of the update. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Deleting rows from a table not contained in another table
I want to reimplement DELETE FROM foo; INSERT INTO foo SELECT * FROM bar; in a way which does not touch rows which are not modified (mainly to avoid locking issues). I've come up with this: DELETE FROM foo WHERE NOT EXISTS (SELECT * FROM bar WHERE foo.* IS NOT DISTINCT FROM bar.*); INSERT INTO foo SELECT * FROM bar EXCEPT SELECT * FROM foo; The problem is that the plan for the DELETE doesn't look pretty at all: QUERY PLAN --- Nested Loop Anti Join (cost=313.36..181568.96 rows=1 width=6) Join Filter: (NOT (foo.* IS DISTINCT FROM bar.*)) -> Seq Scan on foo (cost=0.00..293.05 rows=20305 width=38) -> Materialize (cost=313.36..516.40 rows=20305 width=32) -> Seq Scan on bar (cost=0.00..293.05 rows=20305 width=32) (5 rows) Is there some way to turn this into a merge join, short of introducing primary keys and using them to guide the join operation? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Jagged/ragged arrays
It seems that PostgreSQL 8.4 does not support ragged arrays. Is there a workaround to get similar functionality (mainly the ability to extract values in SQL expressions)? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Jagged/ragged arrays
* Craig Ringer:
> On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote:
>> Hey Florian,
>>
>> What do you mean by "ragged" arrays?
>
> At a guess:
>
> craig=> SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer[][];
> ERROR: multidimensional arrays must have array expressions with
> matching dimensions
>
> (OP) Correct?
Yes, this is what I'm after.
--
Florian Weimer
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is there a solution for "SELECT OR INSERT"
* Stefan Becker: > Is there a way to get the ID row OR create a new one in > ONE single statement? You could create a stored procedure. But if you have concurrent inserts, locking is a bit tricky. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Transaction-specific global variable
hstore greatly simplifies creating triggers for logging table changes, which is great. However, when creating a log record, I would like to include information about the party who made this change. We generally do not allow direct database access for application code, so the PostgreSQL user does not provide sufficient information on its own. Instead, I'd like to create a transaction-specific variable which stores context information to be included in the log table. I suppose I could create a stored procedures in C which provides this functionality, but I wonder if there is already something similar I could reuse. For instance, I could reuse the application_name configuration variable, but this seems a bit gross. (If you could recommend logging functionality I could learn from, that would be welcome, too.) -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
* Leif Biberg Kristensen:
> And even better, in the first comment to the blog post, I was advised about
> the
> SETVAL() function which does exactly what I wanted in the first place.
>
> CREATE SEQUENCE persons_person_id_seq;
> SELECT SETVAL('persons_person_id_seq', MAX(person_id)) FROM persons;
> ALTER TABLE persons ALTER COLUMN person_id SET DEFAULT
> NEXTVAL('persons_person_id_seq');
> ALTER SEQUENCE persons_person_id_seq OWNED BY persons.person_id;
I think you should acquire an exclusive lock on the table, too.
--
Florian Weimer
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new table with a select
* Julien Cigar: > create table foo as select * from bar; > > just add "where 1=2" if you just want the schema There's also this: CREATE TABLE foo (LIKE bar); This gives you more control over what aspects of the table are duplicated; see the documentation for details. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using bitmap index scans-more efficient
* Kyle Bateman: > Any ideas about whether/how this can be done? If the project tree is fairly consistent, it's convenient to encode it using intervals instead of parent/child intervals. IIRC, Celko's "SQL for smarties" explains how to do this, and Kristian Koehntopp has written some PHP code to implement it. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
* Tom Lane: > The CREATE TABLE reference page further amplifies: > > PostgreSQL allows a table of no columns to be created (for example, > CREATE TABLE foo();). This is an extension from the SQL standard, which > does not allow zero-column tables. Zero-column tables are not in > themselves very useful, but disallowing them creates odd special cases > for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec > restriction. And you need the syntax for table partitioning. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Selecting exactly one row for each column value
I've got the following table: fweimer=> SELECT * FROM tab; a | b | c ---+---+--- 1 | 2 | 3 5 | 6 | 7 1 | 2 | 2 2 | 3 | 4 1 | 2 | 2 2 | 3 | 4 For each value in the first column, I need one (and only one) matching row from the table. A possible solution is: a | b | c ---+---+--- 5 | 6 | 7 2 | 3 | 4 1 | 2 | 3 Of course, SELECT a, (SELECT b FROM tab i WHERE i.a = o.a LIMIT 1), (SELECT c FROM TAB i WHERE i.a = o.a LIMIT 1) FROM tab o GROUP BY o.a; does the trick, but this approach seems to rely on undefined behavior and quickly gets messy when the number of columns increases. Is there a better way to implement this? -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Selecting exactly one row for each column value
* Tom Lane: > Florian Weimer <[EMAIL PROTECTED]> writes: >> For each value in the first column, I need one (and only one) matching >> row from the table. A possible solution is: > > SELECT DISTINCT ON would do it, if you don't mind a non-portable solution. Cool, thanks a lot. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(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] Index on elements of an array
Is it possible to create an index on the elements of an array, or a functional index on a set-returning function? The index only needs to speed up queries for specific elements (using a simple membership test, position in the array does not matter) and perhaps range queries. The indexed types include integers and text strings, and possibly more (IP addresses, for instance). The number of elements per indexed array rarely exceeds 4, I think. I fear that the distribution of values makes the intarray module and full text search inappropriate choices (lots of values unique to a specific row). -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Generating table rows from arrays
Is there a convenient way to turn an array into table rows? For
example, I've got an array like {1, 2, 3} and would like to insert
rows:
('aaa', 1)
('aaa', 2)
('aaa', 3)
The first row is constant. I could write a loop with PL/pgsql, I
guess, but I wonder if there are better options nowadays.
--
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generating table rows from arrays
* Richard Huxton: >> The first row is constant. I could write a loop with PL/pgsql, I >> guess, but I wonder if there are better options nowadays. > > The smallest function I've seen is Merlin Moncure's here: > > http://people.planetpostgresql.org/merlin/index.php?/archives/4-fun-with-arrays.html Ah, neat trick. Thanks. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
