[SQL] Partitioned Tables
I'm using partitioned tables where a child table is chosen based on a time stamp. One child table exists for each month. I move records from a temp table to the partitioned table via something like this: INSERT INTO parent_table SELECT * FROM temp_table; All works well but with when inserting to standard tables the insert result contains the number of records inserted. I've found that when I insert into partitioned tables the result is always zero. I'm using the TCL binding (pgintcl) but psql behaves the same way. Is there any way to determine the number of rows actually inserted? Thanks, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] can this be done with a check expression?
I have a table with 3 columns: name text start_id integer end_id integer start_id and end_id are ranges which must not overlap but can have gaps between them. Is it possible to formulate a table check constraint that can verify that either id does not fall within an existing range at insert time? IE prevent overlaps during insert? Thanks, Wayne -- 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] can this be done with a check expression?
Thanks for the input. I don't insert into this table that often so I'll just prevent overlaps at the application level since I'm running 9.0.X and not really in a position to upgrade right now. Thanks again, Wayne On Fri, Aug 03, 2012 at 11:50:13AM -0400, Jonathan S. Katz wrote: > On Aug 2, 2012, at 7:10 PM, Wayne Cuddy wrote: > > > I have a table with 3 columns: > > > > name text > > start_id integer > > end_id integer > > > > start_id and end_id are ranges which must not overlap but can have gaps > > between them. Is it possible to formulate a table check constraint that > > can verify that either id does not fall within an existing range at > > insert time? IE prevent overlaps during insert? > > > > Thanks, > > Wayne > > So this answer will not help you for the here-and-now, but Postgres 9.2 is > going to be released in the near future (though the beta is available) and > contains "range types" which have check constraints on them: > > http://www.postgresql.org/docs/9.2/static/rangetypes.html#RANGETYPES-CONSTRAINT > > You could formulate a check constraint right now to do the equivalent, albeit > it will involve a lot of conditions. > > Jonathan -- 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] can this be done with a check expression?
I'm seeing this in my PGSQL logs, is this something to be concerned about? I know the file system it sits on is reliable and the DB appears to run with fine, additionally the log indicates it's a debug message. I did some google searches and didn't find much. When I examine the file system file by these names to not exist. Thanks, Wayne -- 2012-07-19 21:05:42.840 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45365979_fsm" but retrying: No such file or directory 2012-07-20 06:25:43.899 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45413298_fsm" but retrying: No such file or directory 2012-07-20 11:20:44.755 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45438221_fsm" but retrying: No such file or directory 2012-07-20 15:40:45.974 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45460199_fsm" but retrying: No such file or directory 2012-07-22 12:35:43.882 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45687933_fsm" but retrying: No such file or directory 2012-07-25 03:35:42.933 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46007387_fsm" but retrying: No such file or directory 2012-07-25 06:30:45.354 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46022167_fsm" but retrying: No such file or directory 2012-07-26 06:48:13.595 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46145719_fsm" but retrying: No such file or directory 2012-07-28 17:58:40.774 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46445945_fsm" but retrying: No such file or directory 2012-08-01 09:38:42.234 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46890581_fsm" but retrying: No such file or directory 2012-08-05 01:07:43.659 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47334522_fsm" but retrying: No such file or directory 2012-08-07 01:27:43.605 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47579627_fsm" but retrying: No such file or directory -- 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] can this be done with a check expression?
This was posted under the wrong subject.. Please ignore, will repost. On Wed, Aug 08, 2012 at 11:41:01AM -0400, Wayne Cuddy wrote: > I'm seeing this in my PGSQL logs, is this something to be concerned > about? I know the file system it sits on is reliable and the DB appears > to run with fine, additionally the log indicates it's a debug message. I > did some google searches and didn't find much. > > When I examine the file system file by these names to not exist. > > Thanks, > Wayne > > -- > 2012-07-19 21:05:42.840 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45365979_fsm" but retrying: No such file or directory > 2012-07-20 06:25:43.899 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45413298_fsm" but retrying: No such file or directory > 2012-07-20 11:20:44.755 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45438221_fsm" but retrying: No such file or directory > 2012-07-20 15:40:45.974 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45460199_fsm" but retrying: No such file or directory > 2012-07-22 12:35:43.882 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45687933_fsm" but retrying: No such file or directory > 2012-07-25 03:35:42.933 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46007387_fsm" but retrying: No such file or directory > 2012-07-25 06:30:45.354 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46022167_fsm" but retrying: No such file or directory > 2012-07-26 06:48:13.595 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46145719_fsm" but retrying: No such file or directory > 2012-07-28 17:58:40.774 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46445945_fsm" but retrying: No such file or directory > 2012-08-01 09:38:42.234 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46890581_fsm" but retrying: No such file or directory > 2012-08-05 01:07:43.659 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/47334522_fsm" but retrying: No such file or directory > 2012-08-07 01:27:43.605 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/47579627_fsm" but retrying: No such file or directory > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] fsync debug messages in pgsql logs
I'm seeing this in my PGSQL logs, is this something to be concerned about? I know the file system it sits on is reliable and the DB appears to run with fine, additionally the log indicates it's a debug message. I did some google searches and didn't find much. When I examine the file system file by these names to not exist. Thanks, Wayne -- 2012-07-19 21:05:42.840 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45365979_fsm" but retrying: No such file or directory 2012-07-20 06:25:43.899 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45413298_fsm" but retrying: No such file or directory 2012-07-20 11:20:44.755 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45438221_fsm" but retrying: No such file or directory 2012-07-20 15:40:45.974 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45460199_fsm" but retrying: No such file or directory 2012-07-22 12:35:43.882 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45687933_fsm" but retrying: No such file or directory 2012-07-25 03:35:42.933 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46007387_fsm" but retrying: No such file or directory 2012-07-25 06:30:45.354 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46022167_fsm" but retrying: No such file or directory 2012-07-26 06:48:13.595 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46145719_fsm" but retrying: No such file or directory 2012-07-28 17:58:40.774 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46445945_fsm" but retrying: No such file or directory 2012-08-01 09:38:42.234 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46890581_fsm" but retrying: No such file or directory 2012-08-05 01:07:43.659 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47334522_fsm" but retrying: No such file or directory 2012-08-07 01:27:43.605 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47579627_fsm" but retrying: No such file or directory -- 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] fsync debug messages in pgsql logs
On Wed, Aug 08, 2012 at 12:23:22PM -0400, Tom Lane wrote: > Wayne Cuddy writes: > > I'm seeing this in my PGSQL logs, is this something to be concerned > > about? I know the file system it sits on is reliable and the DB appears > > to run with fine, additionally the log indicates it's a debug message. I > > did some google searches and didn't find much. > > When I examine the file system file by these names to not exist. > > If it only complains once per file name, this is expected behavior when > somebody drops a table just before the checkpoint mechanism tries to > fsync it. (If the failure were to repeat, then it might be something > more interesting.) It does seem a bit odd that only fsm files are being > complained of, though. > > What PG version is that exactly? > > regards, tom lane It's 9.0.4. I'm frequently truncating tables but not dropping them. -- 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] fsync debug messages in pgsql logs
Ok, I'll keep an eye on it but I'm not so worried now. Thanks Tom. On Wed, Aug 08, 2012 at 12:57:01PM -0400, Tom Lane wrote: > Wayne Cuddy writes: > > On Wed, Aug 08, 2012 at 12:23:22PM -0400, Tom Lane wrote: > >> If it only complains once per file name, this is expected behavior when > >> somebody drops a table just before the checkpoint mechanism tries to > >> fsync it. (If the failure were to repeat, then it might be something > >> more interesting.) It does seem a bit odd that only fsm files are being > >> complained of, though. > >> > >> What PG version is that exactly? > > > It's 9.0.4. I'm frequently truncating tables but not dropping them. > > Under the hood, TRUNCATE creates a new empty table and then drops the > old one after commit. So that sounds consistent. It's still a bit odd > that all the messages are about FSM files, but maybe that has something > to do with sequence-of-operations in the DROP. Anyway, nothing to see > here AFAICT. > > regards, tom lane > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] locks and "select for update"
The documentation about concurrency states that all the different types of locks are table locks even if the word row appears in the lock name. The documentation about select statements states that when "for update of" is used that only the selected rows will be locked. Is this true or does this really mean that when any target row is found the entire table is locked until the end of the transaction? Thanks, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] transactions and ecpg
I'm looking at some code that a coworker ported from Informix to Postgres 9.0. The Informix database did not have transaction logging enabled thus no begins/aborts/commits/rollbacks where in the original code. The way the code has been ported there are still no begin statements in the code, only commit and rollback after data changing statements are executed. Question 1: After a commit/rollback does the next SQL statement implicitly begin a transaction? Question 2: If we're just examining data via SELECT statements should we be issuing COMMIT statements after or are they only required for data modifying operations? Thanks in advance, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] index scan vs bitmap index scan
I have a table with with an index that is of type 'timestamp without time zone'. Multiple records are inserted per second so the index is not unique. This table experiences frequent inserts and updates. Bulk deletes are performed once per month. Slower than expected search times are experienced when performing queries that limit the result to a range of dates. It's always a simple range: 'where ts between A and B'. I copied the table to another system where no inserts/updates are taking place and the results are rendered much faster. I attribute some of this to the I/O load on the idle system compared to our production system. EXPLAIN shows that the difference is that on the idle system a 'bitmap index scan' is used. On the production system a 'index scan' is used. On the production system query times are greatly reduced when 'set enable_indexscans to off' is used. Both backends are PGSQL 9.0.4. The table has about 24 million records. What influences the use of a bitmap index scan vs index scan? Any pointers on what I can do to render faster performance without having to explicitly adjust enable_indexscan would be greatly appreciated. Thanks, Wayne -- 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] Need help revoking access WHERE state = 'deleted'
On Thu, Feb 28, 2013 at 06:02:05PM +, Ben Morrow wrote: > Quoth m...@summersault.com (Mark Stosberg): > > > > We are working on a project to start storing some data as "soft deleted" > > (WHERE state = 'deleted') instead of hard-deleting it. > > > > To make sure that we never accidentally expose the deleted rows through > > the application, I had the idea to use a view and permissions for this > > purpose. > > > > I thought I could revoke SELECT access to the "entities" table, but then > > grant SELECT access to a view: > > > > CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state > > != 'deleted'; > > > > We could then find/replace in the code to replace references to the > > "entities" table with the "entities_not_deleted" table > > (If you wanted to you could instead rename the table, and use rules on > the view to transform DELETE to UPDATE SET state = 'deleted' and copy > across INSERT and UPDATE...) Ben, Sorry to barge in but I'm just curious... I understand this part "transform DELETE to UPDATE SET state = 'deleted'". Can you explain a little further what you mean by "copy across INSERT and UPDATE..."? > > > However, this isn't working, I "permission denied" when trying to use > > the view. (as the same user that has had their SELECT access removed to > > the underlying table.) > > Works for me. Have you made an explicit GRANT on the view? Make sure > you've read section 37.4 'Rules and Privileges' in the documentation, > since it explains the ways in which this sort of information hiding is > not ironclad. > > Ben Thanks, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
