Re: [GENERAL] C++Builder table exist
On 2013-03-13, Charl Roux charl.r...@hotmail.com wrote:
--_51d77859-0e03-4afa-bde6-853bee9c0a11_
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
appologies for the formatting, gmane did something to your email that
SLRN didn't like.
void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
int errorCode;
TStringList *tableList = new TStringList;
frmDataModule-eyeConnection-GetTableNames(tableList);
// create queryBackup table if does not exist
AnsiString SQL = CREATE TABLE queryBackup( queryName VARCHAR(30) PRIMARY
KEY ,query VARCHAR(1));
if( tableList-IndexOf(queryBackup) 0 )
errorCode = frmDataModule-eyeConnection-ExecuteDirect(SQL);
}
ERROR: relation querybackup already exists.
The problem is you're checking for queryBackup and then creating
querybackup when it doesn't exist. (note: capitalisation)
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
This case-folding is one of the few places where postgres deliberately
breaks the sql standard (AIUI standard wants case folded upwards).
--
⚂⚃ 100% natural
--
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] DB design advice: lots of small tables?
On 2013-03-15, lender crlen...@gmail.com wrote: Hello. We are currently redesigning a medium/large office management web application. There are 75 tables in our existing PostgreSQL database, but that number is artificially low, due to some unfortunate design choices. The main culprits are two tables named catalog and catalog_entries. They contain all those data sets that the previous designer deemed too small for a separate table, so now they are all stored together. The values in catalog_entries are typically used to populate dropdown select fields. So, my first main question would be: is it normal or desirable to have that many tiny tables? And is it a problem that many of the tables have the same (or a similar) column definitions? Dunno about normal, but certainly Normal (as in -form). No problem. The second point is that we have redundant unique identifiers in catalog_entries (id and code). The code value is used by the application whenever we need to find to one of the values. For example, for a query like show all open invoices, we would either - 1) select the id from catalog_entries where catalog_id refers to the invoice_status catalog and the code is open 2) use that id to filter select * from invoices - or do the same in one query using joins. This pattern occurs hundreds of times in the application code. From a programming viewpoint, having all-text ids would make things a lot simpler and cleaner (i.e., keep only the code column). The id column was used (AFAIK) to reduce the storage size. Most of the data tables have less than 100k records, so the overhead wouldn't be too dramatic, but a few tables (~10) have more; one of them has 1.2m records. These tables can also refer to the old catalog_entries table from more than one column. Changing all these references from INT to VARCHAR would increase the DB size, and probably make scans less performant. I'm not sure know how indexes on these columns would be affected. To summarize, the second question is whether we should ditch the artificial numeric IDs and just use the code column as primary key in the new tiny tables. I if they aren't hurting you keep them. Thanks in advance for your advice. If you're worried about clutter It may make sense to put all the small tables in a separate schema. -- ⚂⚃ 100% natural -- 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] Addled index
On 16 March 2013 01:21, Steve Crawford scrawf...@pinpointresearch.comwrote: On 03/15/2013 11:29 AM, Oleg Alexeev wrote: We've faced with strange index problem. At some moment index became bad and queries does not return any data. For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains identical values for A.id = B.a_id. A.name column has unique constraint and additional index by it. So, in some moment results for queries like [select id from A where name = 'petya'] became empty (row with 'petya' name exist in A). But query [select a_id from B where name = 'petya'] returns A.id and [select * from A where id = found id] returns row. This problem can be solved by index recreation only. How can we avoid such situation? What version?? The first one fail was on 9.1.? (table with at least 10 000 000 rows with 20% every day modifications) Two day ago was another one fail on 9.2.3. (table with 120 000 rows with less than 0.5% every day modifications) -- Oleg V Alexeev E:oalex...@gmail.com
Re: [GENERAL] Testing Technique when using a DB
On 2013-03-13, Joe Van Dyk j...@tanga.com wrote: --047d7b6226a405604904d7d09001 Content-Type: text/plain; charset=UTF-8 On Wed, Mar 13, 2013 at 8:47 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 03/12/2013 09:05 PM, Perry Smith wrote: To all who replied: Thank you. ... I had not seriously considered pg_dump / pg_restore because I assumed it would be fairly slow but I will experiment with pg_restore and template techniques this weekend and see which ones prove viable. Another possibility a bit outside my area of expertise but what about a VM image configured to your needs that you just spin up as needed then discard when done (i.e. always spinning up the same starting image)? I'd guess the OP is running hundreds of tests, where the data needs to be reverted/reset after each test, and each individual test might run in, say, 0.1 seconds. This is a really common technique when testing web apps. I don't think you'd want to start a VM for each of these tests, especially when the tests are small and specific. A vm rewinds to a snapshot in a few seconds this will likely be faster than any other way* if the database is large. *except possibly a similar trick using ZFS snapshots may be faster. -- ⚂⚃ 100% natural -- 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] Addled index
On Mar 16, 2013, at 9:33, Oleg Alexeev oalex...@gmail.com wrote: On 16 March 2013 01:21, Steve Crawford scrawf...@pinpointresearch.com wrote: On 03/15/2013 11:29 AM, Oleg Alexeev wrote: We've faced with strange index problem. At some moment index became bad and queries does not return any data. For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains identical values for A.id = B.a_id. A.name column has unique constraint and additional index by it. So, in some moment results for queries like [select id from A where name = 'petya'] became empty (row with 'petya' name exist in A). But query [select a_id from B where name = 'petya'] returns A.id and [select * from A where id = found id] returns row. This problem can be solved by index recreation only. How can we avoid such situation? What version?? The first one fail was on 9.1.? (table with at least 10 000 000 rows with 20% every day modifications) Two day ago was another one fail on 9.2.3. (table with 120 000 rows with less than 0.5% every day modifications) Perhaps the name you're not finding is spelled differently than what you're typing, due to collation? If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it: Maybe you turned fsync off? What type of index is that? A standard btree or one of the newer types? Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
[GENERAL] Analyzing the types of prepared statements
Hello List, My colleagues and I have some interest in creating an automated PG-Haskell bridge, that will read the PG catalog for a particular namespace or namespaces and generate a Haskell module with functions of appropriate types, creating functions for each stored procedure and simple tuple types for each table and view. It would also be nice to scan all the source code for queries and, ECPG-like, generate code for each query and replace the query with a reference to it. As long as we can ask Postgres to inspect a SQL statement for its argument types and return type (if it has one), it's straightforward to generate the code and replace the inline SQL statements (which are marked with quasi-quotations, and thus distinguishable syntactically from plain strings). However, it is not clear to me at this juncture how to get the return type for a statement, given its text. Preparing and looking it up in pg_prepared_statements will retrieve the argument types but not the return type. Wrapping the query in a SQL stored procedure which returns record doesn't cause the server to infer and store a more specific type. It is possible to find the return type of a select by replacing the parameters with NULLs, wrapping the statement in CREATE TEMP VIEW and examining the type of the view. For statements involving INSERT RETURNING or DELETE RETURNING, though, this technique is not viable (no data modifying statements allowed in WITH clauses for views). To be able to examine inserts and deletes too, one could drop all constraints on all tables and then create a temp table that selects from a CTE with the to-be-analyzed statement in it, where all '?' have been replaced by NULL. But I'm sure I'm not the first person who's had this problem and I wonder if there's a better way. I'd prefer to be able to run the analysis step on a DB without changing it. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- 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] Addled index
Alban Hertroys haram...@gmail.com writes: If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it: Maybe you turned fsync off? What type of index is that? A standard btree or one of the newer types? Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node? More generally: since we're not hearing this type of complaint from other people, there must be something pretty unusual about your installation. You've provided no information that would suggest what, though. Aside from Alban's questions, some other things come to mind: * is that a plain text column, or some other data type? * what collation/ctype is your database using? * what nondefault parameter settings are you using? * where did you get the Postgres executables from? Some distro (whose)? If they're self-built, what compiler and configuration settings did you use? * what platform is this? I would not rule out kernel bugs or flaky hardware. regards, tom lane -- 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] Addled index
On 16 March 2013 14:32, Alban Hertroys haram...@gmail.com wrote: On Mar 16, 2013, at 9:33, Oleg Alexeev oalex...@gmail.com wrote: On 16 March 2013 01:21, Steve Crawford scrawf...@pinpointresearch.comwrote: On 03/15/2013 11:29 AM, Oleg Alexeev wrote: We've faced with strange index problem. At some moment index became bad and queries does not return any data. For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains identical values for A.id = B.a_id. A.name column has unique constraint and additional index by it. So, in some moment results for queries like [select id from A where name = 'petya'] became empty (row with 'petya' name exist in A). But query [select a_id from B where name = 'petya'] returns A.id and [select * from A where id = found id] returns row. This problem can be solved by index recreation only. How can we avoid such situation? What version?? The first one fail was on 9.1.? (table with at least 10 000 000 rows with 20% every day modifications) Two day ago was another one fail on 9.2.3. (table with 120 000 rows with less than 0.5% every day modifications) Perhaps the name you're not finding is spelled differently than what you're typing, due to collation? If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it: Maybe you turned fsync off? What type of index is that? A standard btree or one of the newer types? Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node? Oh, it is not about short experiments. :) Both failed queries are part of 24/7 application. And one of the query results became empty for existing key at some moment. We've recreated index and same query returned to the normal work. fsync is in off state, yes Both failed indexes are btree type. Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node. -- Oleg V Alexeev E:oalex...@gmail.com
Re: [GENERAL] Addled index
On 16 March 2013 19:10, Tom Lane t...@sss.pgh.pa.us wrote: Alban Hertroys haram...@gmail.com writes: If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it: Maybe you turned fsync off? What type of index is that? A standard btree or one of the newer types? Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node? More generally: since we're not hearing this type of complaint from other people, there must be something pretty unusual about your installation. You've provided no information that would suggest what, though. Aside from Alban's questions, some other things come to mind: * is that a plain text column, or some other data type? * what collation/ctype is your database using? * what nondefault parameter settings are you using? * where did you get the Postgres executables from? Some distro (whose)? If they're self-built, what compiler and configuration settings did you use? * what platform is this? I would not rule out kernel bugs or flaky hardware. regards, tom lane * it is varchar columns, 256 and 32 symbols length * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, checkpoint_completion_target = 0.7 * postgres 9.2.3 installed via yum repository for version 9.2 * 64 bit Centos 6, installed and updated from yum repository -- Oleg V Alexeev E:oalex...@gmail.com
[GENERAL] How to use daterange type?
Hi, I'm using postgresql 9.2. I'm trying to figure out how can I use daterange type in my database that is supposed to be a school calendar. I did the followings at the postgresql command prompt: create database schoolcalendar; create table semester_1 ( schooldays daterange ); insert into semester_1 values ( '[2012-09-01, 2012-12-24]' ); So how can I use this table further eg. to get dates of the school days but without Saturdays and Sundays? -- Regards from Pal -- 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] Addled index
Oleg Alexeev oalex...@gmail.com writes: * it is varchar columns, 256 and 32 symbols length * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, checkpoint_completion_target = 0.7 * postgres 9.2.3 installed via yum repository for version 9.2 * 64 bit Centos 6, installed and updated from yum repository fsync off? Have you had any power failures or other system crashes? ext4 is *way* more prone than ext3 was to corrupt data when fsync is disabled, because it caches and reorders writes much more aggressively. Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node. Meh. I quote from the RHEL6 documentation (Storage Administration Guide, Chapter 20: Solid-State Disk Deployment Guidelines): Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not recommended for use on SSDs. https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/newmds-ssdtuning.html The part of the docs I'm looking at only asserts that performance is bad, but considering that it's a deprecated combination, it may well be that there are data-loss bugs in there. I'd certainly suggest making sure you are on a *recent* kernel. If that doesn't help, reconsider your filesystem choices. (Disclaimer: I work for Red Hat, but not in the filesystem group, so I don't necessarily know what I'm talking about. But I have the feeling you have chosen a configuration that's pretty bleeding-edge for RHEL6.) regards, tom lane -- 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] How to use daterange type?
2013/3/17 Csanyi Pal csanyi...@gmail.com: Hi, I'm using postgresql 9.2. I'm trying to figure out how can I use daterange type in my database that is supposed to be a school calendar. I did the followings at the postgresql command prompt: create database schoolcalendar; create table semester_1 ( schooldays daterange ); insert into semester_1 values ( '[2012-09-01, 2012-12-24]' ); So how can I use this table further eg. to get dates of the school days but without Saturdays and Sundays? I don't think there's a built-in way of doing that. You could write a function which takes the daterange as an argument and iterates between the daterange's lower and upper bounds but skipping dates which are Saturdays and Sundays. Regards Ian Barwick -- 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] How to use daterange type?
On Mar 16, 2013, at 2:05 PM, Csanyi Pal wrote:
So how can I use this table further eg. to get dates of the school days
but without Saturdays and Sundays?
You can't do that directly (that kind of calendar operation is outside of the
scope of a range type). You can, however, easily write selects that handle
that:
postgres=# SELECT count(*)
postgres-#FROM generate_series(lower('[2012-09-01,
2012-12-24]'::daterange)::timestamp, upper('[2012-09-01,
2012-12-24]'::daterange)::timestamp, '1 day') as day
postgres-#WHERE EXTRACT(dow FROM day) BETWEEN 1 AND 5;
count
---
82
(1 row)
In cases where you have more complex calendars (like lists of bank holidays),
you could join against a table of them, or use a function that determines
whether or not a particular day is holiday or not.
--
-- Christophe Pettus
x...@thebuild.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
