[GENERAL] DB design advice: lots of small tables?
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. The catalog table (simplified): id SERIAL -- artificial record ID codeVARCHAR-- unique text identifier description VARCHAR-- (info only, otherwise unused) The catalog_entries table (simplified): id SERIAL -- artificial record ID catalog_id INTEGER-- parent catalog ID codeVARCHAR-- unique (per catalog) text identifier rankINTEGER-- used for sorting the values text_short VARCHAR-- text for display (short version) text_long TEXT -- text for display (long version) Here are some examples of what the tables contain: Catalog: department Entries: it, sales, accounting, cases, ... Catalog: printers Entries: ma_color, pa_color, pa_black, pdf, ... Catalog: invoice_status Entries: open, locked, entered, booked, cancelled, ... Catalog: coverage Entries: national, regional, international, obsolete Records in other tables reference the values in catalog_entries by id. For example, the "invoices" table has a "status_id" column pointing to a record in catalog_entries. Of course, this leads to possible integrity issues (there is nothing to prevent an invoice record referencing the "ma_color" value instead of "open" in its status_id field). There are 64 "catalogs" (data sets), in addition to the 75 real tables. Now we have finally got the go-ahead to refactor this mess. Most of the old "catalogs" will probably end up as separate tables. Others might be replaced with ENUMs or booleans, especially the ones with only 2-3 values. The reason why I'm hesitating and asking for advice now, is that after refactoring, we'll end up with ~60 new tables, all of them rather small and with practically identical columns. (Only five catalogs have more than 20 entries; about half have five entries or less; five catalogs have only two entries) 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? 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. Thanks in advance for your advice. crl -- 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?
Thanks everybody for your comments. On 2013-03-15 16:03, François Beausoleil wrote: > Keeping a meaningless ID is not a problem in and of itself. It makes > it easier to edit records from the UI, since you can reference the ID in > the UPDATE and DELETE statements, without fear of colliding with > anything else. It's not so much a problem on small lookup tables, but on > larger entities (people, companies, etc), referencing through the ID is > much, much easier. I'm not so much concerned with giving records in a large data set surrogate IDs. There is often no good candidate for a natural key, or the candidates aren't static enough. The small tables I mentioned earlier all have clear and very static keys (the "code" column). I'm only concerned about the practical consequences of introducing strings where there used to be integers. To give a practical example, this is how it looks with artificial IDs: Table documents: id| name| type_id ---+-+-- 62307 | Example.odt | 413 Table document_types: id | code | text_short -+--+--- 413 | information_disclosure_statement | Information Disclosure [...] Using the natural key, it would look like this: Table documents: id | name | type +--+-- 23 | Example.odt | information_disclosure_statement Table document_types: code | text_short --+--- information_disclosure_statement | Information Disclosure [...] (admittedly, "information_disclosure_statement" is one of the longer codes we use. The average is about 14 characters, the longest is 38 characters) Now, what if we have hundreds of thousands of records in the "documents" table? Apart from the increased storage requirements, will scanning the table take noticably longer? Will the indexes suffer? Will creating, updating, importing, dumping, restoring etc take (much) longer? Comparing two integers is computationally less expensive than comparing two variable-length strings, of course, but I have no empirical notion of how much of a performance hit to expect. I know that these questions cannot be answered with any accuracy without knowing all the details, but that's the type of thing that has me a little worried right now. I'm fine with getting a little less performance; that should be mostly offset by the other changes and improvements we're making. I just don't want to introduce a fundamental mistake at this stage. Thanks again, crl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_stat_get_last_vacuum_time(): why non-FULL?
According to the manual (9.1), pg_stat_get_last_vacuum_time() returns timestamptz | Time of the last non-FULL vacuum initiated by the | user on this table Why are full vacuums excluded from this statistic? It looks like there's no way to get the date of the last manual vacuum, if only full vacuums are performed. regards, crl -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-03-26 19:28, Kevin Grittner wrote: >> Why are full vacuums excluded from this statistic? It looks like there's >> no way to get the date of the last manual vacuum, if only full vacuums >> are performed. > > Because FULL is a bit of a misnomer -- there are important things a > non-FULL vacuum does which a FULL vacuum does not. In general, a > VACUUM FULL should be followed by a non-FULL vacuum to keep the > database in good shape. Thank you, that's very helpful. I wasn't aware of that. > Also, a VACUUM FULL is an extreme form of > maintenance which should rarely be needed; if you find that you > need to run VACUUM FULL, something is probably being done wrong > which should be fixed so that you don't need to continue to do such > extreme maintenance. In this case I was only trying to make sense of an existing database (8.3). The statistics in pg_stats were way off for some tables, so I wanted to see if (auto)vacuum and (auto)analyze were being run. pg_stat_all_tables() showed last_autoanalyze at >400 days for some of the larger tables. There used to be a weekly cron job with VACUUM FULL ANALYZE, and I was trying to find out if that cron job was still active. Thanks, crl -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-03-28 13:11, Martín Marqués wrote: > 2013/3/27 CR Lender : >> In this case I was only trying to make sense of an existing database >> (8.3). The statistics in pg_stats were way off for some tables, so I >> wanted to see if (auto)vacuum and (auto)analyze were being run. >> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of >> the larger tables. There used to be a weekly cron job with VACUUM FULL >> ANALYZE, and I was trying to find out if that cron job was still active. > > What's your autovacuum configuration? autovacuum_vacuum_threshold? > autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor? > autovacuum_analyze_scale_factor? autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold| 50 autovacuum_freeze_max_age | 2 autovacuum_max_workers | 3 autovacuum_naptime | 1min autovacuum_vacuum_cost_delay| 20ms autovacuum_vacuum_cost_limit| -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 The database is running on PostgreSQL 8.3.6. I don't maintain this server, and my knowledge about the autovacuum feature and its settings are sketchy. The values above could be the defaults for 8.3.6, or they may have been adjusted by the admin. > Related to your 400+ days not vacuumed tables, are you sure those > tables have data changes (INSERT/UPDATE/DELETE)? I have some static > tables with over a year of no vacuum (and autovacuum field never ran > on that relation). Yes. Autovacuum and autoanalyze are active, and tables with frequent DELETEs and UPDATEs appear to be vacuumed and analyzed at least once a day. Other tables with relatively frequent INSERTs, but irregular UPDATEs and rare DELETEs go without vacuum/analyze for long periods of time. Static tables never get analyzed or vacuumed (as expected). > What does n_dead_tup show? Here are the statistics for three exemplary tables: relname: | r | oe | mv ||| n_tup_ins| 35335 | 179507 | 9562 n_tup_upd| 46727 | 824898 | 0 n_tup_del| 0 | 9709 | 3567 n_tup_hot_upd| 2016 | 793169 | 0 n_live_tup | 206086 |1132164 | 57964 n_dead_tup | 35583 | 46932 | 5436 last_autovacuum | 2011-05-25 | NULL | NULL last_autoanalyze | 2013-01-07 | 2012-12-27 | 2012-04-16 I'm not saying that autovacuum/autoanalyze aren't working as designed, I was just surprised by the long delays. Concerning the earlier reply to my question... > On 2013-03-26 19:28, Kevin Grittner wrote: >> Because FULL is a bit of a misnomer -- there are important things a >> non-FULL vacuum does which a FULL vacuum does not. In general, a >> VACUUM FULL should be followed by a non-FULL vacuum to keep the >> database in good shape. I've read the manual more carefully now, and I can't see any mention of what VACUUM does that VACUUM FULL does not. The point about extreme maintainance is taken, but from what I read, VACUUM FULL should include everything a normal VACUUM does. Thanks, crl -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-03-28 20:44, Kevin Grittner wrote: > CR Lender wrote: > >> The database is running on PostgreSQL 8.3.6. > >> I've read the manual more carefully now, and I can't see any mention of >> what VACUUM does that VACUUM FULL does not. The point about extreme >> maintainance is taken, but from what I read, VACUUM FULL should include >> everything a normal VACUUM does. > > Prior to release 9.0 that is probably true. Hm, I can't find it, even in the manual for 9.2. http://www.postgresql.org/docs/current/static/sql-vacuum.html If VACUUM FULL is just a more aggressive VACCUM (including writing new data files), then I don't understand the "non-FULL" restriction in pg_stat_get_last_vacuum_time()... unless that information is somehow lost when table files are rewritten. > 8.3 is out of support now. Even for the 8.3 release, 8.3.6 is > missing over four years of fixes for bugs and security > vulnerabilities. There is a very good chance that any problem you > see already fixed and you are just choosing to run without the fix. You're right of course, the PostgreSQL version on the server is rather old. We're redesigning the whole application, and migrating to 9.2 will be part of the process (I'm running 9.1 locally). Thanks, crl -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-03-31 18:31, CR Lender wrote: > On 2013-03-28 20:44, Kevin Grittner wrote: >> CR Lender wrote: >>> I've read the manual more carefully now, and I can't see any mention of >>> what VACUUM does that VACUUM FULL does not. The point about extreme >>> maintainance is taken, but from what I read, VACUUM FULL should include >>> everything a normal VACUUM does. >> >> Prior to release 9.0 that is probably true. > > Hm, I can't find it, even in the manual for 9.2. > http://www.postgresql.org/docs/current/static/sql-vacuum.html > > If VACUUM FULL is just a more aggressive VACCUM (including writing new > data files), then I don't understand the "non-FULL" restriction in > pg_stat_get_last_vacuum_time()... unless that information is somehow > lost when table files are rewritten. I don't mean to be pushy, but I have a meeting with the admin of that database tomorrow, and it would be nice if I had something concrete to tell him. I still don't know what it is that VACCUM does but VACUUM full doesn't do. There's nothing in the manual about that. Thanks, crl -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-04-09 00:09, Kevin Grittner wrote: > I'm not sure that what we're doing now is correct, but updating > things as if a normal vacuum had been done would *not* be the thing > to do. For starters, VACUUM FULL blows away the free space map and > visibility map for a table. Among other things, that means that > index-only scans will cease to work until the table has a normal > vacuum. Ah, now it makes sense. Thank you, that's what I was looking for. And I agree with Jeff that this could be documented in more detail. Thanks, crl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Checking for changes in other tables
I have two tables with countries and persons living in those countries:
create table countries (
codechar(2) not null primary key,
eu boolean not null
);
insert into countries values
('AR', false),
('BE', true),
('CH', false),
('DE', true);
create table persons (
nametextnot null primary key,
country char(2) not null references countries(code)
);
insert into persons (name, country) values
('Arthur', 'AR'),
('Betty', 'BE'),
('Charlie', 'CH'),
('Diane', 'DE');
Enter a third table for loans that can only be made between persons
living in EU countries:
create table eu_loans (
donor textnot null references persons(name),
recipient textnot null references persons(name),
primary key (donor, recipient)
);
insert into eu_loans (donor, recipient) values
('Diane', 'Betty');
I can add a trigger on eu_loans to check if Diane and Betty both live in
the EU. The problem is how to prevent one of them from moving to a
non-EU country (if they do, the loan has to be cancelled first). They
are however allowed to move to other EU countries.
At the moment, this is checked by the application, but not enforced by
the database. I could add more triggers to the persons table (and
another one on countries), but that doesn't "feel" right... countries
and persons are base data and shouldn't need to "know" about other
tables using their records. Ideally, eu_loans would have a check
constraint to verify that its contents remain valid.
Is there any way to ensure that all donors and recipients in eu_loans
are in the EU, without altering the countries and persons tables?
Thanks for any suggestions.
crl
--
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] Checking for changes in other tables
On 2013-04-26 12:17, D'Arcy J.M. Cain wrote: > On Fri, 26 Apr 2013 11:01:28 +0200 > CR Lender wrote: >> create table countries ( >> codechar(2) not null primary key, > > Isn't this redundant? Primary keys are always NOT NULL. Yes, I forgot to remove the NOT NULL when I adjusted the example. > Side question - are you really limiting them to one loan each? Can't a > donor have two active loans with the same recipient? This is just a very reduced example structure (I wouldn't make a person's first name the primary key, either :-). The actual case doesn't even involve persons or loans, but it's far too complex to be used as an example. It took weeks for me to understand how everything in that database was (supposed to be) connected. >> I can add a trigger on eu_loans to check if Diane and Betty both live >> in the EU. The problem is how to prevent one of them from moving to a >> non-EU country (if they do, the loan has to be cancelled first). They >> are however allowed to move to other EU countries. > > Wouldn't two constraints, one for each of donor and recipient, do the > job? Moving a person out of the EU would have the same effect as > deleting them. The constraint would prevent it. I'm not sure I'm following... how would such a constraint look like? Thanks, crl -- 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] Checking for changes in other tables
Sorry for the late reply, I had to give this some time to digest. I'm still trying to wrap my head around the concept that triggers don't "belong" to a table... On 2013-04-26 17:15, Richard Huxton wrote: > On 26/04/13 10:01, CR Lender wrote: > I think this is more a problem of terminology rather than your current > triggers. Triggers aren't really "part" of a table, but they are > observing it, so it's a sensible place to list them when viewing a > table-definition in psql. There's no reason the trigger function is even > in the same schema as the targetted table. Okay, that's true for the trigger function (procedure), but not for the trigger itself. As far as I can tell, triggers aren't directly adressable, except through their tables. I can have two separate triggers with the same name (in the same schema) on different tables. > How would it feel if the syntax was more like the following? > > CREATE TRIGGER ... OBSERVING UPDATES ON persons ... > > or even > > PUBLISH UPDATE,INSERT,DELETE ON persons AS person_changes; > SUBSCRIBE TO person_changes CALLING PROCEDURE ...; > > A different "feel", but no difference in behaviour. Yes, I see your point; that's how it would look if triggers were completely separate from their tables, in a pub/sub way. I guess I wouldn't have a bad feeling about this if I could define them like that. On the other hand: triggers can't just subscribe to anything, they can only react to events on a single table; they are automatically and silently deleted when the table they are observing is dropped; they can be enabled or disabled via ALTER TABLE, not ALTER TRIGGER. AFAICS, there's also no \d command in psql to list triggers; they are only shown when the observed table is inspected with \d. All of this makes it hard for me to see a trigger as a detached observer rather than a "behavior" of a table, so to speak. I need to think about this some more. Thanks for your help, crl -- 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] Storing Special Characters
On 2013-05-14 19:32, Paul Jungwirth wrote: > The UTF-8 encoding for a pound sign is 0xc2a3, not just 0xa3. You > might want to make sure your PHP file is correct. Just for the record, the Unicode code point for the pound symbol (£) is actually 0x00A3. 0xC2A3 is the Hangul syllable Syuh (슣). - crl -- 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] Storing Special Characters
On 2013-05-15 00:31, Chris Angelico wrote: > Which, in UTF-8, is represented by the byte sequence C2 A3. (The > latter would be represented as EC 8A A3.) Right, my bad. I read Unicode instead of UTF-8. - crl -- 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] Replication
On 23/06/09 03:44, Scott Marlowe wrote: > On Mon, Jun 22, 2009 at 4:51 PM, Gerry Reno wrote: >> Londiste is beta. The fact that Skype uses it is because it's part >> of Skytools which is their product. They may want to run their own >> beta stuff. I don't. > > So, if they said it was general release, but it sucked, you'd try it, > but since they say it's beta, no way? Wow. Just wow. The amount > of dumb in that sentence is not measurable with modern > instrumentation. To be fair, the "beta" label has been abused a lot in the last years; and what's more, it has been used as an excuse to refuse support (I'm looking at Google here). Another point would be that Skype has come under attack for using what basically amounts to a black box protocol in their main application - many security-minded people are sceptical of the company for this reason, and I can't blame them. That said, I do use pgbouncer, which is also a Skype project (released under the BSD license). After some casual code review I found it to be of good quality, and I'm now using it in production environments. I don't think it's so unreasonable to be questioning projects which are only available as "betas". There was a time when "beta" meant caveat emptor, this product is not fully tested, and if it breaks, we'd like to hear about it, but we won't be surprised. Trusting such a product with database replication may well work, but it's a risk not everybody's willing to take. - Conrad -- 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 know if a query is semantically correct without execute it?
On 2013-09-19 02:01, Juan Daniel Santana Rodes wrote: > I am programming a procedure in plpgsql language and it must return a > boolean response. The procedure must return TRUE if the query is > semantically correct and if not correct, return FALSE. This must be > done without running the query. You could use EXPLAIN instead of running the actual query, catching the eventual errors and returning TRUE or FALSE depending on the outcome. I'm not sure what you mean by "semantically correct", but in addition to simple syntax errors, non-existent relations, columns, functions, etc, would also be caught. regards, CRL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] standard_conforming_strings and pg_escape_string()
Hi.
I'm using PostgreSQL 8.3 with PHP's "pgsql" module (libpq 8.3.7). When
the server's standard_conforming_strings setting is off (this is
currently still the default, I believe), I use something like this to
escape strings:
if ($escWildcards) {
$str = strtr($str, array("%" => '\%', "_" => '\_'));
}
return "E'" . pg_escape_string($str) . "'";
I would like our database abstraction to be able to handle both settings
for standard_conforming_strings transparently, i.e. perform the escaping
according to the current DB server settings. Since pg_escape_string() is
aware of the current database connection, I had expected its behavior to
change accordingly:
no std strings:
x\y --> x\\y
with std strings:
x\y --> x\y
Unfortunately, this doesn't happen. Isn't pg_escape_string() the
preferred way to escape strings for PostgreSQL in PHP?
And finally, would it be safe to always use the E'\\' syntax, regardless
of how standard_conforming_strings is set on the server?
- Conrad
--
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] standard_conforming_strings and pg_escape_string()
Tom,
thanks for your reply.
On 24/04/09 00:56, Tom Lane wrote:
>> if ($escWildcards) {
>> $str = strtr($str, array("%" => '\%', "_" => '\_'));
>> }
>> return "E'" . pg_escape_string($str) . "'";
>
> The above cannot possibly work. pg_escape_string is generating what it
> supposes to be a normal string literal, and then you are sticking an 'E'
> on the front which changes the escaping rules. It is not the function's
> fault that this fails.
I'm afraid I don't understand why it fails (it appears to work, at
least). I have to enclose the result of pg_escape_string() in single
quotes to get a string literal, and if I don't add the "E" in front, I
see warnings in the server log about "nonstandard use of \\ in a string
literal" (standard_conforming_strings is off, escape_string_warning is on).
I could disable the warnings, of course, but I suppose they are there
for a reason.
Thanks,
- Conrad
--
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] standard_conforming_strings and pg_escape_string()
On 24/04/09 14:49, Daniel Verite wrote:
> It works for me:
>
> $ php -e
> echo phpversion(), "\n";
> $c=pg_connect("dbname=mail user=daniel host=/tmp port=5000");
> pg_query("SET standard_conforming_strings=off");
> echo pg_escape_string('toto\titi'), "\n";
> pg_query("SET standard_conforming_strings=on");
> echo pg_escape_string('toto\titi'), "\n";
> ?>
>
> Output:
> 5.2.0-8+etch13
> toto\\titi
> toto\titi
Very interesting! That's exactly what I had hoped would happen, but for
some reason it didn't. I've managed to replicate this with a standalone
test script, which means that here must be something else going wrong in
our application.
Thank you all for your help!
- Conrad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "No transaction in progress" warning
Hello. We've recently discovered a bug in our code that resulted in COMMITs without BEGINs, and our client saw "no transaction in progress" warnings in his log file. What worries me is that we didn't discover this problem during development - the warning doesn't show up in our logs. We're both using 8.3, and the settings in both postgresql.conf files are similar (except for memory settings and file locations). SHOW log_min_messages; log_min_messages -- notice (1 row) I thought this would cause all warnings to be logged automatically. When I enter "COMMIT" in psql, I do get the warning, but I don't see it in the log file. What could be the problem? Thanks, - Conrad -- 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] "No transaction in progress" warning
On 07/05/09 15:58, Tom Lane wrote: > What seems most likely is that you're looking at the wrong log file. > Recheck the "where to log" settings. Thanks, Adrian and Tom, you were right, I wasn't looking at the correct file. My postgresql.conf has: log_directory = '/var/log/postgresql' log_filename = 'postgresql-8.3-main.log' The server startup messages were in this file (which is why I assumed it was the correct one), but for some reason after a restart the logs were created as /var/log/postgresql/postgresql-8.3-main.log.1241706461 I've moved away all the old files, but every time I restart (using the /etc/init.d/postgres script), new log files with a timestamp are created. AFAICS, there are no open filehandles or locks left pointing to '/var/log/postgresql/postgresql-8.3-main.log'. Well, at least now I know where to look for the warnings. Thanks, - Conrad -- 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] "No transaction in progress" warning
On 07/05/09 16:56, Adrian Klaver wrote:
>> The server startup messages were in this file (which is why I
>> assumed it was the correct one), but for some reason after a
>> restart the logs were created as
>>
>> /var/log/postgresql/postgresql-8.3-main.log.1241706461
[...]
> is done in the zone specified by log_timezone.) < present, PostgreSQL will append the epoch of the new log file's
> creation time. For example, if log_filename were server_log, then the
> chosen file name would be server_log.1093827753 for a log starting at
> Sun Aug 29 19:02:33 2004 MST.>> This parameter can only be set in the
> postgresql.conf file or on the server command line.
Thanks, I had missed that in the manual.
I'm beginning to feel incredibly dense now, but this actually brought my
original problem back. When I do specify log_filename and log_directory,
and restart Postgres, the test you suggested ('select 1/0;') shows up as
an error in the log file
/var/log/postgresql/postgresql-8.3-main.log.{timestamp}
When I leave both log_directory and log_filename commented out (my
original settings), then restart postgres, it creates the file
/var/log/postgresql/postgresql-8.3-main.log
This contains three lines about SSL certificates, but the warning from
'select 1/0' will instead be written to this file:
/var/lib/postgresql/8.3/main/pg_log/postgresql-2009-05-07_170932.log
So there are two log files :-|
Is there any way to configure Postgres to always append to the same file
(/var/log/postgresql/postgresql-8.3-main.log), even if it doesn't
contain strftime escapes? I guess it must be possible, because that's
the way it used to work (before I screwed up my kernel memory settings
in an unrelated SNAFU, preventing Postgres from starting on boot).
Thanks for your patience.
- Conrad
--
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] "No transaction in progress" warning
On 07/05/09 18:26, Tom Lane wrote:
> Scott Mead writes:
>> How did you restart postgres? I'm guessing that you're using a
>> distribution provided package. If you're using the /etc/init.d
>> scripts from that package, it's likely that the startup script is
>> redirecting stderr and that the system is configured to use syslog
>> for the rest of the logging.
>
> Right. There are a number of messages that can appear at startup
> before the postmaster reads and adopts the "where to log" settings in
> postgresql.conf. Those early messages are going to go to
> postmaster's stderr. What it sounds like is you're using a start
> script that points postmaster stderr to
> /var/log/postgresql/postgresql-8.3-main.log. I don't find that to be
> a tremendously good idea --- in my RPMs the early-startup messages go
> to a fixed file (/var/lib/pgsql/pgstartup.log) that's not dependent
> on what the "where to log" configuration settings are.
I looked into the startup scripts that are used here (Ubuntu 8.10): the
/etc/init.d script calls a Perl script and redirects that command's
stderr to stdin to capture warnings and errors:
ERRMSG=$(pg_ctlcluster 8.3 main start 2>&1)
The pg_ctlcluster script parses postgresql.conf and, if log_filename and
log_directory aren't defined there, adds '-l
/var/log/postgresql/postgresql-8.3-main.log' as an option for pg_ctl.
This file is created with the appropriate permissions if necessary.
The script then forks, and the child detaches itself from the terminal
and redirects stdout and stderr to /dev/null:
setsid;
dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 1);
dup2(POSIX::open('/dev/null', POSIX::O_WRONLY), 2);
exec $pg_ctl @options;
That doesn't look too bad to me, or at least it's how I would write a
daemon script, too. If I understand you correctly, stderr should be left
intact?
If this is not the preferred way to handle logging with Postgres, maybe
I should ask pg_ctlcluster's maintainer about it.
Regards,
- Conrad
--
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] "No transaction in progress" warning
On 08/05/09 16:43, Conrad Lender wrote: > stderr to stdin to capture warnings and errors: That should be "stderr to stdout", of course. - Conrad -- 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] Code tables, conditional foreign keys?
On 23/05/09 06:05, Rodrigo E. De León Plicet wrote: >>> Is there a better way? >> >> Yeah, natural keys. > > +1. > > Also, what Ben described reeks of EAV. > > Ben, please read: > > http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html Ah yes, the great Celko. I had the honor of being "lectured" by him on a similar topic, and to tell the truth, I'm still unconvinced. I had inherited a generally well-designed database with a clear relational structure, apart from one table about which I was uncertain. This table collected several groups of attributes/values that were not necessarily related to each other, and could (should?) be split into separate tables. It was nowhere near as bad as the example in the linked article, and we did have check constraints in place. The values were used to classify and sometimes "tag" rows in other tables. I hesitated to break this table up, because that would have meant - doubling the number of existing tables (70 to 140) - significant code changes all over the place - having a lot of very small tables with identical layout "id" (surrogate key) "entry" (string; used to refer to this row by the application) "label" (string; visible on the front end) Here's an example of the value groups that were contained in the table: fax status: pending, active, sent, error department: office, accounting, it, legal, experts deadline type: official, unofficial ... Using the "entry" field as natural keys would have been possible in some places, but not everywhere, and the labels still had to be editable by the users. Some of the "entry" strings were rather verbose (>40 characters), which would have made the other tables a lot larger. It also didn't "feel right" to have so many tiny tables with only 2-5 rows. Is it really advisable to put all these values into 70 separate tables with the exact same layout? I don't quite see the benefit. - Conrad -- 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] Code tables, conditional foreign keys?
On 23/05/09 09:34, Scott Marlowe wrote: > I have a great deal of respect for Celko. I don't always agree with > everything he says, but most of it makes a lot of sense to me. I didn't intend any disrespect to Joe Celko. I have read a number of his articles, which tend to be well written and informative. Last year, when I posted to comp.databases asking for advice on whether to refactor that table, he wrote "You will have to throw it all out and start over with a relational design", "Throw away the idiot who did the EAV. This is not a good design -- in fact, it is not a design at all", and "This is basic stuff!!" Then he copied the same EAV example that was linked earlier by Rodrigo, claiming that "someone like me" had suggested it. With all the respect I have for Mr. Celko, that was hardly helpful, as that example and the situation I had described were quite different. It also did not encourage me to follow his advice and start from scratch (and fire my boss, who was the mentioned "idiot"). I understand the problems that can arise from bad design choices, and I know that Celko is vehemently opposed to anything that resembles EAV, but I felt that in our case "throwing it all away" would be excessive. We had safeguards to ensure referential integrity, and keeping the values in the same table allowed us to let users manage them all with the same form. So I guess it's like Stefan Keller said in a different thread today: "Know when to break the rules." - Conrad -- 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] Code tables, conditional foreign keys?
On 26/05/09 20:48, Benjamin Smith wrote:
> "A deep unwavering belief is a sure sign that you're missing
> something." -- Unknown
>
> I had no intention of sparking an ideological discussion.
I know, my apologies for going off-topic. I just had a deja-vu when I
saw Celko's article about EAV disasters mentioned again in a very
similar context.
> So back to the first question: is there a way to have a
> conditional foreign key?
I can only suggest what we've done in this situation. We had a table
setup similar to yours (tables like "customer" referencing many small
sets like customer type or education level, with the exact same
structure). All of the small sets were combined in one table (which is
what reminded people of EAV design). Using your original example ...
create table codetables
(
id serial primary key,
name varchar unique not null
);
create table codevalues
(
id serial primary key,
codetables_id integer not null references codetables(id),
value varchar not null,
unique(codetables_id, value)
);
create table customers
(
customer_types_id integer not null references codevalues(id),
customer_taxcode_id integer references codevalues(id),
)
... you need to make sure that customer_types_id references the correct
codetable set within codevalues. To do this, we added CHECK constraints
in our tables:
CREATE TABLE customer (
...
customer_type_id INTNOT NULL,
-- this is the standard FK to codevalues
CONSTRAINT fk_customer_type_id
FOREIGN KEY (customer_type_id)
REFERENCES codevalues (id),
-- this makes sure that the correct set is referenced
CONSTRAINT check_customer_type
CHECK (belongs_to_codetable('customer_type', customer_type_id))
);
CREATE FUNCTION belongs_to_codetable (VARCHAR(255), INT)
RETURNS BOOLEAN
AS '
SELECT EXISTS (
SELECT 1
FROM codetables ct
JOIN codevalues cv
ON cv.codetables_id = ct.id
AND ct.name = $1
AND cv.id = $2
)
' LANGUAGE 'SQL';
We used different names, so this is untested, but in principle it should
do what you require.
Whether this is a good design or not... I'm still not sure. Joe Celko
would grill me for doing something like this.
- Conrad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where do I get the v7.1 beta?
Hi folks If I want to experiment with SSL, it sounds like I need the 7.1 beta. Where do I get it? -- Paul Lender Paul A. Lender University of Minnesota Department of Orthopaedic Surgery 420 Delaware St. S.E., MMC 492 Mayo Minneapolis MN 55455 Voice: (612) 625-1186 FAX: (612) 626-6032 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
