[GENERAL] Is auto-analyze as thorough as manual analyze?
Just had an issue where a prepared query would occasionally choose a very bad plan in production. The same data set in a different environment consistently would choose the index scan. As would be expected, running analyze on that table in production resolved the issue. However, before I ran the analyze I checked pg_stat_user_tables to see last_autoanalyze for that table. It had run today. But the problem existed before that. I would have expected that the auto-analyze would have corrected this (or prevented it entirely if run enough). So that leaves me wondering: is an auto-analyze the same as manually running analyze or is a manual analyze more thorough? This is running version 9.6.3 on Heroku. Thanks, Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Custom shuffle function stopped working in 9.6
On 02/11/2017 11:36 AM, Adrian Klaver wrote: On 02/11/2017 09:17 AM, Alexander Farber wrote: I think ORDER BY RANDOM() has stopped working in 9.6.2: words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest a b c d e f (6 rows) postgres=> select version(); version - PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit (1 row) postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest d c a f e b (6 rows) postgres=> select * from unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest b d e c a f (6 rows) I can duplicate issue on 9.6.2. jack=# select version(); version -- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit (1 row) jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest a b c d e f (6 rows) jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest a b c d e f (6 rows) Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?
On 12/14/2015 11:55 AM, Benjamin Smith wrote: Is there a way to set PG field-level read permissions so that a deny doesn't cause the query to bomb, but the fields for which permission is denied to be nullified? In our web-based app, we have a request to implement granular permissions: table/field level permissions. EG: userX can't read customers.socialsecurity in any circumstance. We'd like to implement DB-level permissions; so far, we've been using an ORM to manage CRUD permissions. This is old hat, but our system has a large number of complex queries that immediately break if *any* field permission fails. So, implementing this for customers could be *very* painful Is that there is a way to let the query succeed, but nullify any fields where read permissions fail? (crossing fingers) We'd be watching the PG logs to identify problem queries in this case. If userX is a real database user you create a customers view in the userX schema that selects from the real customers table and either omits the field entirely or nullifies it. Permissions could be used to deny access to the underlying table, and search_path could be used to avoid most if not all application level changes. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Text to interval conversion can silently truncate data
jack=# select '1.51 years'::interval = '1.52 years'::interval; ?column? -- t (1 row) This is surprising. Once I looked at the C code for Interval it makes more sense given that it cannot represent fractional years, months, or days. Wouldn't it make more sense to raise an invalid input error than to silently truncate data? Jack
[GENERAL] log_statement = 'mod' does not log all data modifying statements
I was recently surprised by changes that were not logged by log_statement = 'mod'. After changing log_statement to 'all', I found that the changes were occurring in a writable CTE. Is there a way to log all statements that update data? Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information
On 05/09/2015 06:33 AM, Stephen Frost wrote: Temporary tables will be in memory unless they overflow work_mem and we do support unlogged tables and tablespaces which you could stick out on a ramdisk if you want. I would suggest not putting a table space on a ramdisk. According to the docs this risks corrupting the entire cluster. http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PLV8 for PostgreSQL 9.4 on Ubuntu 14.04
With PostgreSQL 9.3 I installed plv8 from apt.postgresql.org (http://www.postgresql.org/download/linux/ubuntu/). It doesn't appear that it is available for 9.4. Is this no longer offered or has it just not available yet? Thanks. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcing materialize in the planner
Have you tried putting those components in a common table expression? I'm not sure if it absolutely forces the materialization or not, but in practice that has been my experience. Robert James wrote: I have a query which, when I materialize by hand some of its components, runs 10x faster (including the time needed to materialize). Is there any way to force Postgres to do that? Or do I need to do this by hand using temp tables? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible bug with row_to_json
When using a subquery as a source for row_to_json, depending on the
order of arguments it may ignore renaming a column.
jack=# create table player(
jack(# player_id serial primary key,
jack(# name varchar not null unique
jack(# );
NOTICE: CREATE TABLE will create implicit sequence
player_player_id_seq for serial column player.player_id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
player_pkey for table player
NOTICE: CREATE TABLE / UNIQUE will create implicit index
player_name_key for table player
CREATE TABLE
jack=# insert into player(name) values('Jack');
INSERT 0 1
jack=# select row_to_json(t)
jack-# from (
jack(# select player_id as renamed, name
jack(# from player
jack(# order by name
jack(# ) t;
row_to_json
---
{player_id:1,name:Jack}
(1 row)
It ignored the rename.
jack=# select row_to_json(t)
from (
select name, player_id as renamed
from player
order by name
) t;
row_to_json
-
{name:Jack,renamed:1}
(1 row)
But here it didn't.
Is this a bug?
Jack Christensen
Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?
Joe Van Dyk wrote: Perhaps I fat-fingered something somewhere... I tried that and I got this: https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt The with_filters view uses a different plan. Interesting. It is avoiding the hash join, but it is still evaluating the exists column even when it is not referenced at all in the select. I would have expected the optimizer to remove it entirely. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?
Joe Van Dyk wrote: See https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt for the code. I have promotions(id, end_at, quantity) and promotion_usages(promotion_id). I have a couple of things I typically want to retrieve, and I'd like those things to be composable. In this case, finding recently-expired promotions, finding promotions that have a quantity of one, and finding promotions that were used. My approach is to put these conditions into views, then I can join against each one. But that approach is much slower than inlining all the code. How is this typically done? Thanks, Joe From your first example on the gist I extracted this. It should avoid the multiple scans and hash join the the join of the two views suffers from. create view promotions_with_filters as ( select *, end_at now() - '30 days'::interval as recently_expired, quantity = 1 as one_time_use, exists(select 1 from promotion_usages pu on pu.promotion_id = p.id) as used from promotions ); select count(*) from promotions_with_filters where recently_expired and one_time_use; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Picking the first of an order in an aggregate query
On 12/31/2012 8:33 AM, Robert James wrote: I have a query SELECT grouping_field, MIN(field_a), MIN(field_b) FROM ... GROUP BY grouping_field But, instead of picking the MIN field_a and MIN field_b, I'd like to pick field_a and field_b from the first record, according to an order I'll specify. In pseudo-SQL, it would be something like this: SELECT grouping_field, FIRST(field_a), FIRST(field_b) FROM ... ORDER BY field_c DESC, field_d ASC, myfunc(field_e) ASC GROUP BY grouping_field How can I do that with Postgres? select distinct on (grouping_field), field_a, field_b from ... order by grouping_field, field_a asc, field_b asc http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-DISTINCT -- Jack Christensen http://jackchristensen.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Complex database infrastructure - how to?
On 6/30/2012 9:25 AM, Edson Richter wrote: I've a plan that will need a complex database infra-structure using PostgreSQL 9.1. I've seen similar setups using MS SQL Server and other databases, but all of them support cross database queries (also easy to implement with materialized views). - Administrative database: have few tables, used to administer the infrastructure. This database have some tables like users, groups, permissions, etc. - Application databases: have app specific data. 1) One main Administrative application that will have read/write permissions over the Administrative database. 2) Each application will have to access the application database (for read/write), and the administrative database (for read only - mainly to maintain the record references to the users that created objects, and so on). 3) All applications are written in Java, using JPA for persistence. 4) All databases are running on same server, and all of them have same encoding. What I've tried so far: 1) Copy tables from Administrative to Application: this approach would work, but I have trouble with the foreign keys. I'll have to disable (or drop) them, then copy data, then activate (or recreate them again). Could lead to problems? 2) dblink: I can't use foreign key to foreign tables. Also, it is very hard to implement with JPA. 3) odbc_fdw: along with unstability, difficult to build/deploy, it is too slow (why? - don't know) 4) JPA spacific multi-database approach: not really working, and can't provide database integrity My next try will be using triggers in Administrative database to send data to Application databases using dblink. Is there any ohter way to do that? Please, adivce! Edson. Consider using one database with multiple schemas. You can separate your applications into their own schemas, and you can have cross-schema foreign keys. -- Jack Christensen http://jackchristensen.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Subselect with incorrect column not a syntax error?
On 4/13/2012 11:39 AM, Mike Blackwell wrote: Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike Your subquery is correlated with the outer query. So the c2 in the subquery is referring to table x. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimise PostgreSQL for fast testing
On 2/23/2012 9:22 AM, Simon Riggs wrote: On Thu, Feb 23, 2012 at 5:13 AM, Dmytrii Nagirniakdna...@gmail.com wrote: I wonder if you can suggest me how to speed-up PG when running specs. I asked it at SO here: http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing But briefly. PG specs are2x slower than SQLite. I want it to be on par (don't care about reliability or anything, just need fast specs). Would appreciate some suggestions. You really need to explain why this matters... You mention a typical Ruby on Rails app and then discuss SQLite. Well, typical web apps have more than 1 user, so fairly obviously using SQLite isn't appropriate. If SQLite isn't appropriate, why are you testing with it? How does a test run on a database you aren't using in production tell you anything about the success or otherwise of your program. It doesn't, so saying it runs quicker is irrelevant, surely? Perhaps just run half the test, that would make it twice as quick and still just as valid. If Postgres tests run in ~1 minute, what benefit have you gained from saving 30 seconds? How often are you running tests? So please explain a little more. As another Rails developer using PostgreSQL I think I can explain the use case. In standard Rails usage, the ORM handles all SQL query generation and thus the application is database agnostic. It is typical to use SQLite in development and testing and MySQL or PostgreSQL in production. However, if any PostgreSQL specific functionality is used then obviously PostgreSQL must also be used in development and testing. Another common practice is test-driven development. So the test suite for the application may run scores or hundreds of times per day per developer. So the speed of the test suite is of vital importance to developers. A 30 second difference 100's of times per day really can add up. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Set returning functions in select column list
* Apologies if anyone receives this twice. I previously sent it from another address and it did not appear to go through. Can someone explain how set returning functions in a select clause work? It seems that it is doing some sort of implicit cross join. deliverance_development=# select id, generate_series(1, 3) from users; id | generate_series +- 0 | 1 0 | 2 0 | 3 1 | 1 1 | 2 1 | 3 (6 rows) But if multiple set returning functions that return the same number of rows are in the same select it doesn't further cross join it. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 6 1 | 1 | 4 1 | 2 | 5 1 | 3 | 6 (6 rows) But if the set returning functions return a different number of rows then it goes back to a cross join. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 4 0 | 1 | 5 0 | 2 | 4 0 | 3 | 5 1 | 1 | 4 1 | 2 | 5 1 | 3 | 4 1 | 1 | 5 1 | 2 | 4 1 | 3 | 5 (12 rows) I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either I do not know the key words to search for or it is sparsely documented. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Functions that return a set in select column list
* Apologies if anyone receives this multiple times. I previously sent it with a subject that started with Set and it triggered some sort of admin filter. Can someone explain how set returning functions in a select clause work? It seems that it is doing some sort of implicit cross join. deliverance_development=# select id, generate_series(1, 3) from users; id | generate_series +- 0 | 1 0 | 2 0 | 3 1 | 1 1 | 2 1 | 3 (6 rows) But if multiple set returning functions that return the same number of rows are in the same select it doesn't further cross join it. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 6 1 | 1 | 4 1 | 2 | 5 1 | 3 | 6 (6 rows) But if the set returning functions return a different number of rows then it goes back to a cross join. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 4 0 | 1 | 5 0 | 2 | 4 0 | 3 | 5 1 | 1 | 4 1 | 2 | 5 1 | 3 | 4 1 | 1 | 5 1 | 2 | 4 1 | 3 | 5 (12 rows) I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either I do not know the key words to search for or it is sparsely documented. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Set returning functions in select column list
Can someone explain how set returning functions in a select clause work? It seems that it is doing some sort of implicit cross join. deliverance_development=# select id, generate_series(1, 3) from users; id | generate_series +- 0 | 1 0 | 2 0 | 3 1 | 1 1 | 2 1 | 3 (6 rows) But if multiple set returning functions that return the same number of rows are in the same select it doesn't further cross join it. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 6 1 | 1 | 4 1 | 2 | 5 1 | 3 | 6 (6 rows) But if the set returning functions return a different number of rows then it goes back to a cross join. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users; id | generate_series | generate_series +-+- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 4 0 | 1 | 5 0 | 2 | 4 0 | 3 | 5 1 | 1 | 4 1 | 2 | 5 1 | 3 | 4 1 | 1 | 5 1 | 2 | 4 1 | 3 | 5 (12 rows) I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either I do not know the key words to search for or it is sparsely documented. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why does aggregate query allow select of non-group by or aggregate values?
On 12/9/2011 4:57 PM, David Johnston wrote: Functions are evaluated once for each row that it generated by the surrounding query. This is particularly useful if the function in question takes an aggregate as an input: SELECT col1, array_processing_function( ARRAY_AGG( col2 ) ) FROM table GROUP BY col1; Without this particular behavior you would need to sub-query. From a layman's perspective the reason why you cannot use non-aggregates outside of GROUP BY it that it is ambiguous as to what value to output; with an uncorrelated function call that is not the case. David J. Thanks. This makes sense now. I also went back to the original query that provoked this question. It had a correlated subquery in the select statement. I thought that this could yield ambiguous results. But when I examined it closely, all the correlated fields were included in the group by of the outer query, and when I tried to use a non-grouped column from the outer query I correctly got a ERROR: subquery uses ungrouped column foo from outer query Thanks again. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does aggregate query allow select of non-group by or aggregate values?
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran
into this using an array produced by a subquery as a column in the
select of an aggregate query, but I was able to boil it down to this
contrived example. Shouldn't any expression that is not in the group by
or an aggregate function be rejected?
What am I not understanding?
Thanks.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Failure controlling PG 9.1 service on Ubuntu 10.04 32-bit
On 10/8/2011 1:21 AM, Craig Ringer wrote: On 10/08/2011 02:23 AM, Jack Christensen wrote: Just upgraded a machine from PostgreSQL 9.0 to 9.1. I uninstalled the old version then installed the new one. Whenever I try to run a service command to start, stop, or restart the server it fails. jackc@lizard:~$ sudo service postgresql stop * Stopping PostgreSQL 9.1 database server * Error: Could not open /proc/2193/comm [fail] It seems to be happening in /usr/share/postgresql-common/PgCommon.pm:542 The Upstart scripts and packaging are part of Ubuntu and maintained separately to PostgreSQL. I suggest you file a bug on Launchpad, providing as much detail as you can to assist in identifying why the issue arose. It looks like you were right. There was another update released over the weekend that resolved the problem. -- Craig Ringer -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Failure controlling PG 9.1 service on Ubuntu 10.04 32-bit
Just upgraded a machine from PostgreSQL 9.0 to 9.1. I uninstalled the old version then installed the new one. Whenever I try to run a service command to start, stop, or restart the server it fails. jackc@lizard:~$ sudo service postgresql stop * Stopping PostgreSQL 9.1 database server * Error: Could not open /proc/2193/comm [fail] It seems to be happening in /usr/share/postgresql-common/PgCommon.pm:542 PostgreSQL is actually running fine, but the only way I can make any changes is to reboot the server (or kill all the postgres processes I suppose). -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH x AS (...) and visibility in UPDATE
On 7/27/2011 4:22 PM, Peter V wrote: I want to apply updates on a copy of a row, instead on the row itself. The queries are above were simplied to demonstrate the problem. So basically I want to do: 1) create the copy of the row and return the identifier 2) apply updates on the new row identified by the identifier returned in step 1 If possible, I want to write this in a single command, to avoid overhead and mistakes. I tried writing a rewrite rule or before trigger, but it becomes quickly a mess to avoid infinite loops. Any ideas are welcome. Thanks. Maybe I'm totally missing something, but why insert a copy and then update instead of directly insert a mutated copy? Something like: INSERT INTO t (foo, bar) SELECT 'my new foo', t.bar FROM t WHERE id=123; Wouldn't the above construction let you make a new row with some new values and some copied values? -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On 5/20/2011 8:41 AM, Seb wrote: Hi, I'm trying to implementing the checking in and checking out of items in a table, whereby an item cannot be checked out if it's not checked-in. I've searched for schemas for public libraries where this is a key requirement, but haven't managed to hit the right keywords to get relevant results. Thanks, Use a loans table with unique partial index to ensure that only one unreturned loan per item can exist at a time. CREATE TABLE items( item_id SERIAL PRIMARY KEY, ... ); CREATE TABLE loans( loan_id SERIAL, item_id integer NOT NULL REFERENCES items, start_time timestamptz NOT NULL, end_time timestamptz ... ); CREATE UNIQUE INDEX ON loans(item_id) WHERE end_time IS NULL; -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On 5/20/2011 10:15 AM, Seb wrote: On Fri, 20 May 2011 09:48:45 -0500, Jack Christensenja...@hylesanderson.edu wrote: Use a loans table with unique partial index to ensure that only one unreturned loan per item can exist at a time. [...] Thanks, this certainly avoids loaning an item before it's returned, but it doesn't protect against having loans that overlap in time. For example, an item can have a start_time that is between start_time and end_time of a previous loan for that same item. My first thought was to have some CHECK constraint with a query, but this doesn't seem to be supported by postgresql. In a similar project I worked on start time for a loan was always the current time so overlaps weren't an issue. I don't have any firsthand experience with them, but it sounds like what you want are exclusion constraints. http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table relationship constraints
On 5/5/2011 3:26 PM, Rick Genter wrote: Hm. I think the way I would handle this is to put the business logic for inserting/updating into the room_assignments table into one or more functions and have a special user that owns the tables and owns the functions and declare the functions to be SECURITY DEFINER. Revoke INSERT/UPDATE/DELETE access to the tables from all other users. Then you grant your regular users EXECUTE access to the functions. The functions run as the user that created them, so they will have direct INSERT/UPDATE/DELETE access to the tables while your regular users won't. Thanks everyone for your advice. I think this type of approach will be very helpful. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multiple table relationship constraints
What is the best way to handle multiple table relationships where attributes of the tables at the ends of the chain must match? Example: CREATE TABLE achievements( achievement_id serial PRIMARY KEY, ... ); CREATE TABLE achievement_versions( achievement_version_id serial PRIMARY KEY, achievement_id integer NOT NULL REFERENCES achievements, ... ); CREATE TABLE achievement_attempts( achievement_attempt_id serial PRIMARY KEY, achievement_version_id integer NOT NULL REFERENCES achievement_versions, ... ); CREATE TABLE actions( action_id serial PRIMARY KEY, ... ) CREATE TABLE achievement_attempt_actions( achievement_attempt_id integer NOT NULL REFERENCES achievement_attempts, action_id integer NOT NULL REFERENCES actions, PRIMARY KEY( achievement_attempt_id, action_id) ); The achievement_attempt_actions table links actions to achievement_attempts. For a link to be valid a number of attributes of actions must match attributes of achievements and achievement_attempts. This means an update to any of these 5 tables could invalidate the chain. How can I eliminate the possibility for this type of erroneous data? I have come up with 4 possibilities. 1. Composite keys -- I could include all the attributes that must match on all the tables through the chain and let foreign key constraints handle it. This could work but it feels wrong to be duplicating attributes. It also is inconvenient (but possible) with my ORM. 2. Triggers -- I can use triggers to check every change on all 5 tables that could possibly cause an invalid chain. I have done this before and it does work -- but it can be error prone. 3. Check a materialized view -- Add triggers to all 5 tables to keep a materialized view up to date. Check constraints could validate the materialized view. 4. Validate application side -- this can work well, but it leaves the hole of a bug in the application or a direct SQL statement going bad. Anyone have any advice on the best way to handle this? -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table relationship constraints
On 5/5/2011 2:28 PM, Rick Genter wrote: On Thu, May 5, 2011 at 3:20 PM, Jack Christensen ja...@hylesanderson.edu mailto:ja...@hylesanderson.edu wrote: What is the best way to handle multiple table relationships where attributes of the tables at the ends of the chain must match? Example: CREATE TABLE achievements( achievement_id serial PRIMARY KEY, ... ); CREATE TABLE achievement_versions( achievement_version_id serial PRIMARY KEY, achievement_id integer NOT NULL REFERENCES achievements, ... ); CREATE TABLE achievement_attempts( achievement_attempt_id serial PRIMARY KEY, achievement_version_id integer NOT NULL REFERENCES achievement_versions, ... ); CREATE TABLE actions( action_id serial PRIMARY KEY, ... ) CREATE TABLE achievement_attempt_actions( achievement_attempt_id integer NOT NULL REFERENCES achievement_attempts, action_id integer NOT NULL REFERENCES actions, PRIMARY KEY( achievement_attempt_id, action_id) ); The achievement_attempt_actions table links actions to achievement_attempts. For a link to be valid a number of attributes of actions must match attributes of achievements and achievement_attempts. This means an update to any of these 5 tables could invalidate the chain. How can I eliminate the possibility for this type of erroneous data? I might not be understanding your question, but isn't that what your foreign key references do? For example, you can't update achievement_attempt_id in the achievement_attempt table if there is an achievement_attempt_actions record that refers to it since that would break the reference. (Not that you want to be updating primary key values in the first place...) The trick is there are additional attributes of actions and achievements such as a category that must match for the link to be valid. These attributes are not part of the primary key of either record and can and do change. -- Rick Genter rick.gen...@gmail.com mailto:rick.gen...@gmail.com -- Jack Christensen ja...@hylesanderson.edu
Re: [GENERAL] Multiple table relationship constraints
On 5/5/2011 2:53 PM, Rick Genter wrote: On Thu, May 5, 2011 at 3:50 PM, Jack Christensen ja...@hylesanderson.edu mailto:ja...@hylesanderson.edu wrote: The trick is there are additional attributes of actions and achievements such as a category that must match for the link to be valid. These attributes are not part of the primary key of either record and can and do change. So your data is denormalized? (The category appears in 2 tables?) Don't do that. Create a view that joins your two tables together instead if you need a single entity that contains data from multiple sources. Then you won't have any of the data integrity issues you're worried about. It's not denormalized. It is an attribute that both tables have that have to match for it to be a valid link. Here's a contrived example: CREATE TABLE dorms( dorm_id serial PRIMARY KEY, gender varchar NOT NULL, ... ); CREATE TABLE people( person_id serial PRIMARY KEY, gender varchar NOT NULL, ... ); CREATE TABLE room_assignments( person_id integer NOT NULL REFERENCES people, dorm_id integer NOT NULL REFERENCES dorms, ... ); Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship. -- Rick Genter rick.gen...@gmail.com mailto:rick.gen...@gmail.com -- Jack Christensen ja...@hylesanderson.edu
[GENERAL] Deferred foreign key constraint downsides
I recently had cause to use a deferred foreign key constraint for the first time. I like it. It seems it could make life simpler, especially when an obstinate ORM insists on doing things in the wrong order. The only downside I can see is it may be harder to track down where a violation occurred since the error won't be raised until commit. Are there any other downsides to just setting all my foreign keys to initially deferred? Thanks. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is the name pseudo column
On 12/15/2010 5:43 PM, Adrian Klaver wrote: On Wednesday 15 December 2010 1:27:19 pm David Fetter wrote: On Wed, Dec 15, 2010 at 01:50:54PM -0600, Jack Christensen wrote: I was just surprised when accidentally selecting a non-existent name column there was no error -- instead something came back. select accounts.name from accounts limit 1 - (1,65522,1,0.00,,2010-07-22 09:57:26.281172-05,2) It appears it tries to return the entire row in an array (but longer rows get truncated). I've searched Google and the PG docs but I haven't had any luck. What happened here is that you ran into PostgreSQL's charming habit of using the argument.function notation, so you called the name function, i.e. the one that casts to name, on the entire row from your accounts table. Cheers, David. In the for what is worth department that behavior is going away in 9.1. See here for a detailed explanation: http://www.depesz.com/index.php/2010/11/08/waiting-for-9-1-removed-autocast-footgun/#more-1908 It makes sense now what's happening -- but glad to see that feature is going away. Thanks. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is the name pseudo column
I was just surprised when accidentally selecting a non-existent name column there was no error -- instead something came back. select accounts.name from accounts limit 1 - (1,65522,1,0.00,,2010-07-22 09:57:26.281172-05,2) It appears it tries to return the entire row in an array (but longer rows get truncated). I've searched Google and the PG docs but I haven't had any luck. -- Jack Christensen ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
