Re: [GENERAL] psql latex and newlines
On Mon, Dec 12, 2011 at 07:15:12PM +0100, Wim Bertels wrote: > Hallo, > > psql latex output format needs to differentiate between a newline and a > tabularnewline. > > the problem arises when u have a field value that contains a newline > character, when this field is not the first column, then all the data > after this newline comes in the first column.. > > u can try this out, writing a function or table, > and then add 'enters' or newline in the COMMENT on this function or > table. > > the \pset recordsep doesn't solve this, since the mistakes take place > within the same record. Can you give me a self-contained test case I can use so I can use it to fix the to code? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Strategy for Primary Key Generation When Populating Table
On Fri, Feb 10, 2012 at 7:49 AM, Vincent Veyron wrote: > Le jeudi 09 février 2012 à 16:30 -0600, Merlin Moncure a écrit : > > > natural/surrogate is a performance/usability debate with various > > tradeoffs. but using surrogate to 'create' uniqueness is a logical > > design error; maybe a very forgivable one for various reasons, but the > > point stands. > > Please consider the following case : > > I record insurance claims in the table below, where id_evenement, > id_agent and date_origine define a unique event. > > However, records sometimes have to be canceled (set annule=true), and > re-recorded the same way. They're normally canceled once, but > occasionnally twice, or more (for various reasons). > > What would you use for a primary key? > > CREATE TABLE tbldossier ( >id_evenement text NOT NULL, >id_agent integer NOT NULL, >date_origine date NOT NULL, >annule boolean DEFAULT false NOT NULL); > > First, a surrogate key will make joins more robust and so it should be there. Also a partial unique index could be used. We've had a similar issue with LedgerSMB and while our solution might not apply to you it's worth mentioning. We had an issue of storing sales tax rates which may change or expire at some point, so something like: CREATE TABLE tax ( id serial not null unique, account_id int not null, rate numeric not null, valid_to date, unique (valid_to, account_id) ); Initially we created a partial unique index on account_id where valid_to is null. Later we changed valid_to to a timestamp and defaulted it to infinity. This allowed us to declare account_id, valid_to as the primary key. Best Wishes, Chris Travers
Re: [GENERAL] " " around fields with psql
On Fri, Feb 10, 2012 at 1:33 PM, Steve Clark wrote:
> On 02/10/2012 02:12 PM, Scott Marlowe wrote:
>
> On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark wrote:
>
> Hello,
>
> Is there a way with psql to get column output to be
> "data1","data2",...,"datan"
>
> I tried -F "," but that left off the first and last quote.
>
> I can't seem to find a way in the man page.
>
> Well, you can do it yourself kinda like this:
>
> select '""||field1||'", "||field2||'" from sometable where yada.
>
> Ok that will work
for 9.1+ you can use built in format() function for a lot of fields:
select format('"%s", "%s", "%s", "%s"', procpid, usename, waiting,
query_start) from pg_stat_activity;
also with recent postgres you can use hstore to convert virtually any
query as such:
select '"' || array_to_string(avals(hstore(a)), '", "') || '"' from
pg_stat_activity a;
postgres=# select '"' || array_to_string(avals(hstore(q)), '", "') ||
'"' from (select 1 as a,2 as b,3 as c) q;
?column?
---
"1", "2", "3"
etc.
merlin
--
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] Strategy for Primary Key Generation When Populating Table
On Feb 10, 2012, at 14:56, Vincent Veyron wrote: > Le vendredi 10 février 2012 à 14:19 -0500, David Johnston a écrit : > >> One possibility is to add a "version" field (integer) and combine evenement >> and version to create the unique. I'd also create a partial unique on >> evenement/annule to ensure you do not make more than one active version. >> > > Hi David, > > > I don't find this solution very much different from using a surrogate > key? > > Except that you then have to code it yourself, instead of using the > built-in serial. > > The version field has semantic meaning that a true sequential value does not. If you are creating new data then you will ultimately always end up with some form of artificial identifier. Since you do not appear to want to assign a new case number when you annul and reopen an event you need some supplemental information to distinguish the two cases. I would argue you should remove annul from the table, optionally replacing it with a "current status" field, and then use some kind of logging table to track changes in status. In that case each event only ever exists once (no versions) and you gain flexibility in handling different statuses (besides just open/annulled). David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.1.1 crash
The following are the relevant log entries from a recent crash of v9.1.1 running on an older RHEL Linux box. This is the first crash we've experienced in a lot of years of running Pg. Any assistance in how to determine what might have caused this is welcome. -- 2012-02-10 13:55:59 CST [15949]: [37-1] @ LOG: 0: server process (PID 32670) was terminated by signal 11: Segmentation fault 2012-02-10 13:55:59 CST [15949]: [38-1] @ LOCATION: LogChildExit, postmaster.c:2881 2012-02-10 13:55:59 CST [15949]: [39-1] @ LOG: 0: terminating any other active server processes 2012-02-10 13:55:59 CST [15949]: [40-1] @ LOCATION: HandleChildCrash, postmaster.c:2695 2012-02-10 13:55:59 CST [15949]: [41-1] @ LOG: 0: all server processes terminated; reinitializing 2012-02-10 13:55:59 CST [15949]: [42-1] @ LOCATION: PostmasterStateMachine, postmaster.c:3116 2012-02-10 13:56:00 CST [3303]: [1-1] @ LOG: 0: database system was interrupted; last known up at 2012-02-10 13:54:18 CST 2012-02-10 13:56:00 CST [3303]: [2-1] @ LOCATION: StartupXLOG, xlog.c:6046 2012-02-10 13:56:00 CST [3303]: [3-1] @ LOG: 0: database system was not properly shut down; automatic recovery in progress 2012-02-10 13:56:00 CST [3303]: [4-1] @ LOCATION: StartupXLOG, xlog.c:6299 2012-02-10 13:56:00 CST [3303]: [5-1] @ LOG: 0: consistent recovery state reached at F/FC9C7588 2012-02-10 13:56:00 CST [3303]: [6-1] @ LOCATION: CheckRecoveryConsistency, xlog.c:6958 2012-02-10 13:56:00 CST [3303]: [7-1] @ LOG: 0: redo starts at F/FC9A5BA8 2012-02-10 13:56:00 CST [3303]: [8-1] @ LOCATION: StartupXLOG, xlog.c:6506 2012-02-10 13:56:00 CST [3303]: [9-1] @ LOG: 0: record with zero length at F/FCC716F0 2012-02-10 13:56:00 CST [3303]: [10-1] @ LOCATION: ReadRecord, xlog.c:3829 2012-02-10 13:56:00 CST [3303]: [11-1] @ LOG: 0: redo done at F/FCC716B4 2012-02-10 13:56:00 CST [3303]: [12-1] @ LOCATION: StartupXLOG, xlog.c:6621 2012-02-10 13:56:00 CST [3303]: [13-1] @ LOG: 0: last completed transaction was at log time 2012-02-10 13:55:59.452228-06 2012-02-10 13:56:00 CST [3303]: [14-1] @ LOCATION: StartupXLOG, xlog.c:6626 2012-02-10 13:56:02 CST [3319]: [1-1] @ LOG: 0: autovacuum launcher started 2012-02-10 13:56:02 CST [3319]: [2-1] @ LOCATION: AutoVacLauncherMain, autovacuum.c:404 2012-02-10 13:56:02 CST [15949]: [43-1] @ LOG: 0: database system is ready to accept connections 2012-02-10 13:56:02 CST [15949]: [44-1] @ LOCATION: reaper, postmaster.c:2435
Re: [GENERAL] Strategy for Primary Key Generation When Populating Table
Le vendredi 10 février 2012 à 14:19 -0500, David Johnston a écrit : > One possibility is to add a "version" field (integer) and combine evenement > and version to create the unique. I'd also create a partial unique on > evenement/annule to ensure you do not make more than one active version. > Hi David, I don't find this solution very much different from using a surrogate key? Except that you then have to code it yourself, instead of using the built-in serial. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- 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] " " around fields with psql
On 02/10/2012 02:12 PM, Scott Marlowe wrote: On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark wrote: Hello, Is there a way with psql to get column output to be "data1","data2",...,"datan" I tried -F "," but that left off the first and last quote. I can't seem to find a way in the man page. Well, you can do it yourself kinda like this: select '""||field1||'", "||field2||'" from sometable where yada. Ok that will work Thanks, -- Stephen Clark *NetWolves* Director of Technology Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] Strategy for Primary Key Generation When Populating Table
On Feb 10, 2012, at 10:49, Vincent Veyron wrote: > Le jeudi 09 février 2012 à 16:30 -0600, Merlin Moncure a écrit : > >> natural/surrogate is a performance/usability debate with various >> tradeoffs. but using surrogate to 'create' uniqueness is a logical >> design error; maybe a very forgivable one for various reasons, but the >> point stands. > > Please consider the following case : > > I record insurance claims in the table below, where id_evenement, > id_agent and date_origine define a unique event. > > However, records sometimes have to be canceled (set annule=true), and > re-recorded the same way. They're normally canceled once, but > occasionnally twice, or more (for various reasons). > > What would you use for a primary key? > > CREATE TABLE tbldossier ( >id_evenement text NOT NULL, >id_agent integer NOT NULL, >date_origine date NOT NULL, >annule boolean DEFAULT false NOT NULL); > > One possibility is to add a "version" field (integer) and combine evenement and version to create the unique. I'd also create a partial unique on evenement/annule to ensure you do not make more than one active version. David J. -- 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] " " around fields with psql
On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark wrote: > Hello, > > Is there a way with psql to get column output to be > "data1","data2",...,"datan" > > I tried -F "," but that left off the first and last quote. > > I can't seem to find a way in the man page. Well, you can do it yourself kinda like this: select '""||field1||'", "||field2||'" from sometable where yada. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] " " around fields with psql
Hello, Is there a way with psql to get column output to be "data1","data2",...,"datan" I tried -F "," but that left off the first and last quote. I can't seem to find a way in the man page. Thanks, -- Stephen Clark *NetWolves* Director of Technology Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
[GENERAL] anyone use pg_rman successfully w/ Postgresql 9.1 64-bit?
I seem to be able to do full backups, but it doesn't show up in the backups list when I do a "show" command and the "validate" command doesn't seem to do anything; doing an incremental backup, it claims the full backup was never validated, so I can't do increments :-P I posted on the pg_rman google group, but it seems to be dead. Operating system is RHEL 6.2 64-bit if that matters. I thought about using OmniPITR but it doesn't have as many features as pg_rman (e.g., incrementals, showing a list of backups so you can choose which point you want to restore to, etc.)...
Re: [GENERAL] Strategy for Primary Key Generation When Populating Table
Le jeudi 09 février 2012 à 16:30 -0600, Merlin Moncure a écrit : > natural/surrogate is a performance/usability debate with various > tradeoffs. but using surrogate to 'create' uniqueness is a logical > design error; maybe a very forgivable one for various reasons, but the > point stands. Please consider the following case : I record insurance claims in the table below, where id_evenement, id_agent and date_origine define a unique event. However, records sometimes have to be canceled (set annule=true), and re-recorded the same way. They're normally canceled once, but occasionnally twice, or more (for various reasons). What would you use for a primary key? CREATE TABLE tbldossier ( id_evenement text NOT NULL, id_agent integer NOT NULL, date_origine date NOT NULL, annule boolean DEFAULT false NOT NULL); -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- 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] [GENERA]: Postgresql-9.1.1 synchronous replication issue
This issue stays resolved !!! The statements are no more hanging on production now :) The suspected problem was - Our brand new production server did not have the port 5432 open. I had opened the port using "iptables" command and everything started working. synchronous replication is fast and awesome. Thanks VB On Fri, Feb 3, 2012 at 9:45 PM, Adrian Klaver wrote: > On Thursday, February 02, 2012 10:21:28 pm Venkat Balaji wrote: > > > > > Connection is working fine between primary and standby, ping is working > > fine and wal archive file transfer is working without any issues. > > > > I tried CREATE TABLE and CREATE DATABASE, both were hanging. > > > > Apart from regular streaming replication settings, I did the following on > > primary to enable synchronous replication - > > > > synchronous_standby_names='*' > > > > Commands started hanging after that. Is there anything else i need to do. > > From here: > > http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html > > " > synchronous_standby_names (string) > ... The synchronous standby will be the first standby named in this list > that is > both currently connected and streaming data in real-time (as shown by a > state of > streaming in the pg_stat_replication view). Other standby servers appearing > later in this list represent potential synchronous standbys > > The name of a standby server for this purpose is the application_name > setting of > the standby, as set in the primary_conninfo of the standby's walreceiver. > There > is no mechanism to enforce uniqueness. In case of duplicates one of the > matching > standbys will be chosen to be the synchronous standby, though exactly > which one > is indeterminate. The special entry * matches any application_name, > including > the default application name of walreceiver. > > " > > So I would check the pg_stat_replication view to see if Postgres is seeing > the > standby as streaming. > > > > > > Thanks > > VB > > -- > Adrian Klaver > adrian.kla...@gmail.com >
