Re: [GENERAL] How to view user defined TYPE
Hi, Any help on the question below? Thanks. On Oct 15, 6:00 pm, Goboxe <[EMAIL PROTECTED]> wrote: > Hi, > > Let say I created a new type: > > CREATE TYPE compfoo AS (f1 int, f2 text); > > How can I view its definition? > > I tried to view it in pgAdmin but seems cannot fine any node that > display that? > > Thanks, > G -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger does not behave as expected
Hi, I have a trigger as below. I am wondering why when I tried to insert to master table with date= 20080908, the trigger does not insert to z_agg_tmcarr_pfx_gtwy_cc_w_20080908. Is that something wrong with the statement "ELSEIF NEW.CallDate >= 20080908 AND NEW.CallDate <= 20080914 THEN"? -- CREATE OR REPLACE FUNCTION t_agg_tmcarr_pfx_gtwy_cc() RETURNS "trigger" AS $BODY$ DECLARE BEGIN IF (TG_OP = 'INSERT') THEN IF NEW.CallDate >= 20080901 AND NEW.CallDate <= 20080907 THEN INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080901 VALUES ( NEW.* ); ELSEIF NEW.CallDate >= 20080908 AND NEW.CallDate <= 20080914 THEN INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080908 VALUES ( NEW.* ); ELSEIF NEW.CallDate >= 20080915 AND NEW.CallDate <= 20080921 THEN INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080915 VALUES ( NEW.* ); END IF; END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION t_agg_tmcarr_pfx_gtwy_cc() OWNER TO sa; -- 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] PostgreSQL on Windows x64
Dave,
Thanks for the link.
Our app now running using EnterpriseDB 8.2 on x86.
Plan to test them on x64 server once we got it.
Do you know if jdbc, npgsql, PostgreSQL ANSI odbc drivers bundled in
EnterpriseDB can run without any issues on x64?
Thanks,
Amin
On Aug 19, 3:50 pm, [EMAIL PROTECTED] ("Dave Page") wrote:
> On Mon, Aug 18, 2008 at 7:48 AM, Goboxe <[EMAIL PROTECTED]> wrote:
> > Hi,
>
> > Does PostgreSQL can run on Windows x64?
> > If yes, which version?
>
> All versions (though you should start with
> 8.3.3):http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_...
>
> --
> Dave Page
> EnterpriseDB UK:http://www.enterprisedb.com
>
> --
> Sent via pgsql-general mailing list ([EMAIL PROTECTED])
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL on Windows x64
Hi, Does PostgreSQL can run on Windows x64? If yes, which version? Thanks, G -- 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] Using syslog on pg for Windows
On Jan 4, 11:22 pm, [EMAIL PROTECTED] ("Jeff Larsen") wrote:
> On Jan 4, 2008 8:33 AM, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> > On Wed, Jan 02, 2008 at 11:17:52PM -0800, Goboxe wrote:
> > > Hi,
>
> > > What are required to be configured in order to send logs to
> > > remote syslog server for pg running on Windows platform?
>
> > > Inhttp://pgfouine.projects.postgresql.org/tutorial.html, it says
> > > "You can set syslog to send the log to another server through the
> > > network with @ip.ad.dr.ess."
>
> > > So try to update postgresql.conf
>
> > > syslog_facility = 'x.x.x.x'
>
> > > or
>
> > > syslog_facility = '[EMAIL PROTECTED]'
>
> > > But it still does not work.
>
> > > Any tips?
>
> > PostgreSQL on Win32 doesn't support syslog logging. You'll have to log to a
> > file and have an outside tool transfer that file to where youn eed it.
>
> PostgreSQL does, however, support logging to Windows Event Log. From
> there you could implement software to ship Event Log to remote syslog.
>
> --
> Jeff
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match- Hide quoted text -
>
> - Show quoted text -
Hi,
Thanks for noth pointers. I'll try it.
Goboxe.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Using syslog on pg for Windows
Hi, What are required to be configured in order to send logs to remote syslog server for pg running on Windows platform? In http://pgfouine.projects.postgresql.org/tutorial.html , it says "You can set syslog to send the log to another server through the network with @ip.ad.dr.ess." So try to update postgresql.conf syslog_facility = 'x.x.x.x' or syslog_facility = '@x.x.x.x' But it still does not work. Any tips? Thanks, Goboxe ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Partitioned tables & Slony
Hi, What need to be taken care of when replicating data from partitioned tables? I have several master tables that are inherited by date either daily, weekly and monthly. How to automate addition of newly created child tables into Slony cluster? Thanks, Goboxe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Partitioned tables & Slony
Thanks Chris. How do I use *.sh & *.ik files? Goboxe On Dec 19, 12:17 am, Chris Browne <[EMAIL PROTECTED]> wrote: > Goboxe <[EMAIL PROTECTED]> writes: > > What need to be taken care of when replicating data from partitioned > > tables? > > > I have several master tables that are inherited by date either daily, > > weekly and monthly. > > > How to automate addition of newly created child tables into Slony > > cluster? > > There's an outline of how to do it, in the partitioning test... > > http://main.slony.info/viewcvs/viewvc.cgi/slony1-engine/tests/testpar... > -- > "cbbrowne","@","linuxdatabases.info"http://linuxfinances.info/info/slony.html > "If we believe in data structures, we must believe in independent > (hence simultaneous) processing. For why else would we collect items > within a structure? Why do we tolerate languages that give us the one > without the other?" -- Alan J. Perlis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Trigger in partitioned table
Hi, When adding sets of new child tables to a artitioned table, I need to update the triggers with new dates added as new condition. See sample below. My question is, what will happen if the update to the trigger happen at the same time as insert operation to the master table? CREATE OR REPLACE FUNCTION t_agg_billing() RETURNS "trigger" AS $BODY$ DECLARE BEGIN IF (TG_OP = 'INSERT') THEN IF NEW.CallDate = 20071001 THEN INSERT INTO z_agg_billing_d_20071001 VALUES ( NEW.* ); ELSEIF NEW.CallDate = 20071002 THEN INSERT INTO z_agg_billing_d_20071002 VALUES ( NEW.* ); ELSEIF NEW.CallDate = 20071003 THEN INSERT INTO z_agg_billing_d_20071003 VALUES ( NEW.* ); ... ... ... END IF; END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION t_agg_billing() OWNER TO sa; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] print command in sproc
Hi, What is the equivalent MSSQL 'print' command in pg sproc? Thanks, G ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partitioned tables, rules, triggers
Josh,
Thanks for sharing a very good info on partitioning.
On Oct 5, 10:08 pm, [EMAIL PROTECTED] ("Josh Tolley") wrote:
> On 10/3/07, Goboxe <[EMAIL PROTECTED]> wrote:
>
> > Hi,
>
> > I have two questions on the above:
>
> > 1. I found in some postings recommended to use triggers instead of
> > rules. Is this documented somewhere?
>
> A howto is available
> athttp://images.omniti.net/omniti.com/talks/partitions-public.pdf
>
> - Josh/eggyknap
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Partitioned tables, rules, triggers
Hi, I have two questions on the above: 1. I found in some postings recommended to use triggers instead of rules. Is this documented somewhere? 2. When using trigger, any examples on how to update the trigger dynamically when tables are added or deleted from the partitioned? Thanks, Amin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Auto-partitioning?
"use consumer called table_dispatcher"... could you elaborate this
further on how to do this?
Thanks,
G
On Aug 22, 3:27 pm, [EMAIL PROTECTED] ("Asko Oja") wrote:
> Hi
>
> Just a hint.
> We do this auto-partitioning with PgQ. Instead of writing records into table
> we push them into queue and use consumer called table_dispatcher to creates
> tartitioned tables as needed and put records into them. We have multiple
> destination databases where to write data and target tables have different
> structures so queue based solution is convenient for us.
>
> Asko
>
> On 8/21/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
>
> > Steve Wampler wrote:
> > > Steve Wampler wrote:
> > >> ... Specifically, I'm wondering if it's
> > >> possible to add a default rule that would create a newpartition
> > >> (with indices, etc.) and would add a new rule for thispartition
> > >> to match the similar ones above (and, of course, then move the
> > >> INSERT into the newpartition).
>
> > > I think I've answered my own question, but would love an expert
> > > to validate the answer:
>
> > > The answer is "no", apparently because 'name' in
>
> > That answer is no but you could probably pull it off with atrigger.
>
> > Sincerely,
>
> > Joshua D. Drake
>
> > - --
>
> > === The PostgreSQL Company: Command Prompt, Inc. ===
> > Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
> > PostgreSQL solutions since 1997 http://www.commandprompt.com/
> > UNIQUE NOT NULL
> > Donate to the PostgreSQL Project:http://www.postgresql.org/about/donate
> > PostgreSQL Replication:http://www.commandprompt.com/products/
>
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.6 (GNU/Linux)
> > Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org
>
> > iD8DBQFGy1BNATb/zqfZUUQRAlhxAJsEpKJicoMkvFXS+T5DiCjroSYj9QCcCjmt
> > qaaXSe764ULKH5h3z8p6QUc=
> > =2xC8
> > -END PGP SIGNATURE-
>
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match- Hide quoted text -
>
> - Show quoted text -
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Partitioned table limitation
On Oct 3, 4:00 am, [EMAIL PROTECTED] ("paul rivers") wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 11:26 AM
> > To: [EMAIL PROTECTED]
> > Subject: Re: [GENERAL]Partitionedtable limitation
>
> > On Oct 2, 1:38 am, [EMAIL PROTECTED] ("paul rivers") wrote:
> > > > -Original Message-
> > > > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > > > [EMAIL PROTECTED] On Behalf Of Goboxe
> > > > Sent: Monday, October 01, 2007 2:18 AM
> > > > To: [EMAIL PROTECTED]
> > > > Subject: [GENERAL]Partitionedtable limitation
>
> > > > Hi,
>
> > > > Are there any limitations on number of child tables that can be use
> > > > in
> > > >partitionedtable?
>
> > > > [snip]
>
> > > We currently use partitioning by date and id, with 1/4 a year of dates
> > and
> > > approximately 10 IDs (and slowly increasing). Each partition runs from
> > > around 1 million to 20 million rows.
>
> > > Whether it's recommended or not, I don't know. But for us, the
> > partitioning
> > > works exactly as advertised. As with anything new, I'd take the time to
> > > setup a simple test to see if it works for you, too.
>
> > > In particular, be sure to check the documentation on caveats. You'll
> > find
> > > these a little stricter than partitioning issues in Oracle or SQL
> > Server.
>
> > > HTH,
> > > Paul
>
> > Thanks Paul for your inputs.
>
> > I am not really clear when you said "partitioning by date and id, with
> > 1/4 a year of dates and
> > approximately 10 IDs". Could you give some examples of your tables?
>
> > TQ,
> > G
>
> Sure.
>
> The largest logical table has a primary key of fw_id, fw_date, fw_line_nbr.
> We partition on fw_id, fw_date.
>
> fw_date ranges from today to about 120 days ago. There are no gaps for any
> fw_id in this rolling window. Each fw_id + fw_date has between 1-20 million
> rows, though most of them tend toward the smaller end of that scale.
>
> We also generate child tables (partitions) for a few days into the future as
> part of a nightly maintenance job. We also drop ones older than the 120
> days. So all told, we have around 1400 partitions or so, and around a
> trillion rows of data, all told. The rows average about 700 bytes or so,
> wide, with date, time, inet, cidr, varchar, bigint smallint, and int types.
>
> There are a variety of different processes loading the data constantly
> during the day. This data is used for ad-hoc troubleshooting during the
> day, plus some near real-time monitoring alerts. It sees a fair amount of
> reading during the day. On a nightly basis, it is rolled up into a
> summarized format, and we keep this rollup data for years. These rollup
> tables arepartitionedtoo, but it's not on the same scale as the above
> table. The rollup data is used for all kinds of trend analysis, further
> reporting, etc.
>
> HTH,
> Paul
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/- Hide quoted text -
>
> - Show quoted text -
Thanks for sharing that.
I will give it try to convert ours to daily table and test its
performance.
G
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Partitioned table limitation
On Oct 2, 1:38 am, [EMAIL PROTECTED] ("paul rivers") wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 2:18 AM
> > To: [EMAIL PROTECTED]
> > Subject: [GENERAL] Partitioned table limitation
>
> > Hi,
>
> > Are there any limitations on number of child tables that can be use
> > in
> > partitioned table?
>
> > [snip]
>
> We currently use partitioning by date and id, with 1/4 a year of dates and
> approximately 10 IDs (and slowly increasing). Each partition runs from
> around 1 million to 20 million rows.
>
> Whether it's recommended or not, I don't know. But for us, the partitioning
> works exactly as advertised. As with anything new, I'd take the time to
> setup a simple test to see if it works for you, too.
>
> In particular, be sure to check the documentation on caveats. You'll find
> these a little stricter than partitioning issues in Oracle or SQL Server.
>
> HTH,
> Paul
>
Thanks Paul for your inputs.
I am not really clear when you said "partitioning by date and id, with
1/4 a year of dates and
approximately 10 IDs". Could you give some examples of your tables?
TQ,
G
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Partitioned table limitation
> > I've played around with as many as 1,000 child tables. By then, the > planning time becomes noticeably longer than for a single table, but > the response time is still so much faster that it's worth it. Note > I'm talking only a fraction of a second planning time, even at 1,000 > tables. > > If you are going over 100 tables, make sure you're using triggers for > updating the child tables not rules, as rules are far too slow when > there's over 50 or so tables to look choose from. > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match- Hide quoted text - > > - Show quoted text - Scott, Could you share a snippet on how to use trigger for this? TQ, G ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Partitioned table limitation
Hi, Are there any limitations on number of child tables that can be use in partitioned table? I am currently having weekly partitioned tables (using partitioned view in SQL Server) that I kept for 2 years. In total, there will be 52 * 2 = 104 tables exist at one time in the partition. I am migrating from SQL Server to pg. Can pg support that number of tables? How about if I want to convert it as daily tables (356 * 2 years = 712 tables). Is this recommended? FYI, currently each weekly table storeing between 28 to 32 millions records (approx 4-5 millions recs daily) Thanks, G ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
