[SQL] join table problem

2010-08-04 Thread Jedrin 

 I have a specific problem with a join table and I've simplified it to
these 3 tables so as not to have to post anything that hints towards
the business nature of our database just in case ..

 At any rate, say I have 3 tables:

table plant
  id:integer
  name string


table seed_supplier
  id: integer
  company_name: string

table plant_seed_supplier
  plant_id
  seed_supplier_id

plant_seed_supplier is a join table that supports a many to many
relationship between the plant table and the seed supplier table since
any plant may have multiple seed suppliers and any seed supplier can
sell seeds for many different plants.

 I would like to be able to write a single query that basically does
this:

 It will show only one distinct row for each plant in the database. It
will show either just one seed_supplier company name associated with
that plant, or if there is no supplier associated with that plant it
will show the plant in a row with that field empty.

What mechanism does this problem call for, a join or nested query, or
what ?

 Thanks




-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Help Need some hindsight

2010-08-04 Thread Andreas 

 Hi,
I need to display log events (again).
The log is simply like this
log ( log_id serial primary key, create_ts timestamp default 
localtimestamp, object_id, state_id, ... )


It records the state of objects and when and what happend to to change 
this state.


I'd like to get a list that shows the current state at any point of time 
and the state of the last event before regarding the current object_id.
The tricky bit is that both states should appear in the same row for 
every row.


Help?
:)


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread John Hasler 
How does PostgreSQL react to time being stepped at bootup?  My Chrony
NTP package might cause it to do so on rare occasions when the hardware
clock is way off.  This would only happen during bootup.
-- 
John Hasler 
[email protected]
Elmwood, WI USA

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] workaround for missing ROWNUM feature with the help of GUC variables

2010-08-04 Thread Marc Mamin 
Hello,

 

here my two pence on this recurring thema.

 

(just a workaround)

 

regards,

 

Marc Mamin

 

 

 

The PG parameter must be set to allow defining own configuration
variables:

 

 

 
#---
---

# CUSTOMIZED OPTIONS

 
#---
---

 

custom_variable_classes = 'public'# list of custom
variable class names

 

 

usage example:



select my_rownum(),* from generate_series (10,15);

 

wrong usage:



select my_rownum() as n1,

   my_rownum() as n2,

   *

   from generate_series (10,15);

   

solution:   



select my_rownum('1') as n1,

   my_rownum('2') as n2,

   *

   from generate_series (10,15);

   

Code:

=

 

CREATE OR REPLACE FUNCTION public.my_rownum ()

returns int AS

$BODY$

 

/*

  

  equivalent to oracle rownum

  (The previous row value is attached to a GUC Variable valid in the
current transaction only)

  quite slow :-(

  

*/  

  

DECLARE

  current_rownum int;

  config_id varchar = 'public.my_rownum';

  

BEGIN

  

  BEGIN

 

current_rownum := cast (current_setting (config_id) as int);

 

  EXCEPTION when others then 

 

return cast( set_config(config_id, cast(1 as text), true) as int);

 

  END;

 

  RETURN  cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);

 

END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;

  

 

/*

-- 

   For multiple usage:

 

-- */  

  

CREATE OR REPLACE FUNCTION public.my_rownum ( id varchar )

returns int AS

$BODY$

  

  /*

  

  equivalent to oracle rownum

  quite slow :-(

  (The previous row value is attached to a GUC Variable valid in the
current transaction only)

  

  $1: when more than one my_rownum is used within a query, each call
must have its own ID in order to get different GUC variable).

  

  */

  

DECLARE

  current_rownum int;

  config_id varchar = 'public.my_rownum'||id;

  

 BEGIN

   

   BEGIN

 

 current_rownum := cast (current_setting (config_id) as int);

 

   EXCEPTION when others then 

 

 return cast( set_config(config_id, cast(1 as text), true) as int);

 

   END;

 

   RETURN  cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);

 

 END;

 $BODY$

   LANGUAGE 'plpgsql' VOLATILE;

Re: [SQL] join table problem

2010-08-04 Thread Oliveiros d'Azevedo Cristina 

Howdy!


At any rate, say I have 3 tables:

table plant
 id:integer
 name string


table seed_supplier
 id: integer
 company_name: string

table plant_seed_supplier
 plant_id
 seed_supplier_id

plant_seed_supplier is a join table that supports a many to many
relationship between the plant table and the seed supplier table since
any plant may have multiple seed suppliers and any seed supplier can
sell seeds for many different plants.

I would like to be able to write a single query that basically does
this:

It will show only one distinct row for each plant in the database. It
will show either just one seed_supplier company name associated with
that plant, or if there is no supplier associated with that plant it
will show the plant in a row with that field empty.



You do not specify what is the seed_supplier company name that should appear 
in the case there is more than one


Give this (untested) query a try

SELECT name,MIN(company_name)
FROM plant a
JOIN plant_seed_supplier associative
ON plant_id = a.id
JOIN seed_supplier b
ON b.id = supplier_id
GROUP BY name

Best,
Oliveiros 



--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread Frank Bax 

John Hasler wrote:

How does PostgreSQL react to time being stepped at bootup?  My Chrony
NTP package might cause it to do so on rare occasions when the hardware
clock is way off.  This would only happen during bootup.



My ntp client changes clock (by small amount) at any time:

Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Help Need some hindsight

2010-08-04 Thread A. Kretschmer 
In response to Andreas :
>  Hi,
> I need to display log events (again).
> The log is simply like this
> log ( log_id serial primary key, create_ts timestamp default 
> localtimestamp, object_id, state_id, ... )
> 
> It records the state of objects and when and what happend to to change 
> this state.
> 
> I'd like to get a list that shows the current state at any point of time 
> and the state of the last event before regarding the current object_id.
> The tricky bit is that both states should appear in the same row for 
> every row.
> 
> Help?


something like:

test=*# select * from log;
 id | ts | object_id | state_id
++---+--
  1 | 2010-08-04 13:24:19.648437 | 1 |1
  2 | 2010-08-04 13:24:26.957629 | 1 |2
  3 | 2010-08-04 13:24:38.883519 | 1 |3
  4 | 2010-08-04 13:24:43.60719  | 1 |2
  5 | 2010-08-04 13:24:51.123276 | 1 |4
(5 rows)

test=*#
test=*#
test=*# 
select max(case when row_number=1 then id else null end) as current_state_id, 
   max(case when row_number=2 then id else null end) as last_state_id, 
   max(case when row_number=1 then state_id else null end) as 
current_state, 
   max(case when row_number=2 then state_id else null end) as last_state 
from ((select 1 as row_number, * from log where object_id=1 and ts < 
'2010-08-04 13:24:38.883520' order by ts desc limit 1) union all 
 (select 2, * fromlog where object_id=1 and ts < '2010-08-04 
13:24:38.883520' order by ts desc limit 1 offset 1)) foo ;
 current_state_id | last_state_id | current_state | last_state
--+---+---+
3 | 2 | 3 |  2
(1 row)

As you can see, the where-condition contains a timestamp that isn't in the 
table, it's after the id=3.

Maybe there are other, better solutions...


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread Kenneth Marshall 
On Wed, Aug 04, 2010 at 07:20:31AM -0400, Frank Bax wrote:
> John Hasler wrote:
>> How does PostgreSQL react to time being stepped at bootup?  My Chrony
>> NTP package might cause it to do so on rare occasions when the hardware
>> clock is way off.  This would only happen during bootup.
>
>
> My ntp client changes clock (by small amount) at any time:
>
> Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
> Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
> Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
> Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s
>

PostgreSQL does not use system time to track transactions so
you should be good. Also, these types of clock changes by ntpd
use the adjtime() system call which either slows or speeds the
system clock to make the adjustment over a period of time so
it should be minimally disruptive. These do seem to be larger
values than you might expect from a clock conditioned with ntpd.
Is it a VM or is there something going on that would stop or
suspend your system?

Cheers,
Ken

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Some quick questions

2010-08-04 Thread Joshua Gooding 
 I am using Postgres 8.4 with 10 partition tables.  We'll call them 
reports_00 through reports_09.


I have a field that is a BIGINT which is a 13 digit number that is the 
epoch time, which is the constraint that the table is partitioned on. 
(Between time x and y).  All of the partitions hold 10 weeks of data.   
The idea is that I would like to write a script that would truncate and 
drop the oldest week's table (after 10 weeks), rename the oldest 
remaining 9 tables, create a new table, with the current and future 
epoch date in the constraint, and continue on my merry way.


Is there anything like this already in postgres?  Secondly can it be 
done without manual intervention?  Can I do this in a function and have 
it auto run at a certain "time" based on epoch?  This is something that 
I have never gotten into so this is new territory for me, so please 
forgive me if I am asking any newbie questions here.


I've tweaked the server that I am testing postgres on.  I'm basically 
doing side by side comparisons with Oracle, trying to see if we can get 
the same or close to Oracle's performance.  I've read the Wiki article 
on tuning the PostgreSQL server, and I believe that I have gotten it 
close, but there is still a substantial gap.  Say I have a machine with 
a 4 core processor and 16GB of ram (across 4 sticks), can I tweak the 
configuration to  use all 4 cores and 1GB of ram from each physical 
stick on the machine?  This is running on a Fedora Core - 12 machine.  
Is that an OS issue or is than a Postgres configuration question?


Any advice or guidance would be greatly appreciated.

--
Joshua Gooding


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Some quick questions

2010-08-04 Thread Kenneth Marshall 
On Wed, Aug 04, 2010 at 10:22:12AM -0400, Joshua Gooding wrote:
>  I am using Postgres 8.4 with 10 partition tables.  We'll call them 
> reports_00 through reports_09.
>
> I have a field that is a BIGINT which is a 13 digit number that is the 
> epoch time, which is the constraint that the table is partitioned on. 
> (Between time x and y).  All of the partitions hold 10 weeks of data.   The 
> idea is that I would like to write a script that would truncate and drop 
> the oldest week's table (after 10 weeks), rename the oldest remaining 9 
> tables, create a new table, with the current and future epoch date in the 
> constraint, and continue on my merry way.
>

We are using a mod() in the CHECK constraint and the trigger to
load a set of tables in cycle: 1->2->3->1... Then we use a cronjob
to truncate the "next" table before data starts to be loaded into
it. The tables are never actually renamed because the mod() function
takes care of the reuse in the correct order.

> Is there anything like this already in postgres?  Secondly can it be done 
> without manual intervention?  Can I do this in a function and have it auto 
> run at a certain "time" based on epoch?  This is something that I have 
> never gotten into so this is new territory for me, so please forgive me if 
> I am asking any newbie questions here.

PostgreSQL does not have built-in time scheduled jobs. You will
need to use cron for that.

>
> I've tweaked the server that I am testing postgres on.  I'm basically doing 
> side by side comparisons with Oracle, trying to see if we can get the same 
> or close to Oracle's performance.  I've read the Wiki article on tuning the 
> PostgreSQL server, and I believe that I have gotten it close, but there is 
> still a substantial gap.  Say I have a machine with a 4 core processor and 
> 16GB of ram (across 4 sticks), can I tweak the configuration to  use all 4 
> cores and 1GB of ram from each physical stick on the machine?  This is 
> running on a Fedora Core - 12 machine.  Is that an OS issue or is than a 
> Postgres configuration question?
>

The OS should manage system resources. PostgreSQL should be tuned
based on the amount of resources available to it.

Cheers,
Ken

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Domains, casts, and MS Access

2010-08-04 Thread Peter Koczan 
Hi all,

I'm working on porting an old MS Access form application from Sybase
to postgres/ODBC as part of a larger database port project.

One of the snags that's popped up is that there's some incompatibility
between data types. Specifically, many fields are the Sybase type
"bit", which is basically a boolean, but it accepts and displays
bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
(especially bareword integers in queries), I've defined a 'sybit' type
in postgres to be a domain.

=> create domain sybit as smallint check ( value in (0,1) );

That is compatible behavior for most applications, but Access gets
confused since it wants to map it to an integer instead of a boolean
(it does the right thing for a native Sybase driver). I thought that
creating casts between sybit and boolean might help, but that won't
work so much it seems.

=# create cast (sybit as smallint) without function as implicit;
CREATE CAST
=# create cast (sybit as integer) with function int4(smallint) as implicit;
CREATE CAST
=# create cast (sybit as boolean) with function bool(integer) as assignment;
ERROR:  argument of cast function must match or be binary-coercible
from source data type

Is there a way to tell Access to do the right thing, or is there a
better way to define the type/domain, or is there some better product
to use?

Thanks much,
Peter

P.S. In case people are interested in the specifics of the sybase
"bit" type, you can look at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks54.htm.

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread John Hasler 
I wrote:
> How does PostgreSQL react to time being stepped at bootup?  My Chrony
> NTP package might cause it to do so on rare occasions when the
> hardware clock is way off.  This would only happen during bootup.

Ken writes:
> PostgreSQL does not use system time to track transactions so you
> should be good.

Thank you.

> Also, these types of clock changes by ntpd use the adjtime() system
> call which either slows or speeds the system clock to make the
> adjustment over a period of time so it should be minimally disruptive.

This is about Chrony , an alternative
ntp implementation.  In any case, both chronyd and ntpd can step the
clock (possibly backwards) at bootup under some rare circumstances.

Frank writes:
> My ntp client changes clock (by small amount) at any time:
> Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
> Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
> Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
> Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s

Ken writes:
> These do seem to be larger values than you might expect from a clock
> conditioned with ntpd.  Is it a VM or is there something going on that
> would stop or suspend your system?

There is certainly something wrong there.
-- 
John Hasler 
[email protected]
Elmwood, WI USA

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Richard Broersma 
On Wed, Aug 4, 2010 at 10:31 AM, Peter Koczan  wrote:

> One of the snags that's popped up is that there's some incompatibility
> between data types. Specifically, many fields are the Sybase type
> "bit", which is basically a boolean, but it accepts and displays
> bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
> (especially bareword integers in queries), I've defined a 'sybit' type
> in postgres to be a domain.

One thought would be see if ODBC configuration options will achieve
this for you. Have you already exhausted this option?


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread Steve Wampler 

John Hasler wrote:


Frank writes:

My ntp client changes clock (by small amount) at any time:
Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s


Ken writes:

These do seem to be larger values than you might expect from a clock
conditioned with ntpd.  Is it a VM or is there something going on that
would stop or suspend your system?



John writes:

There is certainly something wrong there.


I saw very bad clock performance on one Linux box I had (dual-single core
AMD cpus, no VMs), even with NTP, until I changed the clocksource kernel
parameter to hpet.  Unfortunately (or fortunately) I no longer have that box.

--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Peter Koczan 
On Wed, Aug 4, 2010 at 12:47 PM, Richard Broersma
 wrote:
> On Wed, Aug 4, 2010 at 10:31 AM, Peter Koczan  wrote:
>
>> One of the snags that's popped up is that there's some incompatibility
>> between data types. Specifically, many fields are the Sybase type
>> "bit", which is basically a boolean, but it accepts and displays
>> bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
>> (especially bareword integers in queries), I've defined a 'sybit' type
>> in postgres to be a domain.
>
> One thought would be see if ODBC configuration options will achieve
> this for you. Have you already exhausted this option?

This is one of my first forays into ODBC, so I didn't know that was a
possibility. Is there any place where these are documented? Searching
for ODBC options yields info on connection options, but none on
behavior that I could find.

Peter

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Richard Broersma 
On Wed, Aug 4, 2010 at 11:51 AM, Peter Koczan  wrote:

> This is one of my first forays into ODBC, so I didn't know that was a
> possibility. Is there any place where these are documented? Searching
> for ODBC options yields info on connection options, but none on
> behavior that I could find.

I know that there are a couple of options that affect the
representation of Booleans in the odbc driver.  I'm not sure it will
do what you need though.

However, here is the official documentation: (hopefully it helpful)

http://psqlodbc.projects.postgresql.org/


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Justin Graf 
On 8/4/2010 1:56 PM, Richard Broersma wrote:
> On Wed, Aug 4, 2010 at 11:51 AM, Peter Koczan  wrote:
>
>
>> This is one of my first forays into ODBC, so I didn't know that was a
>> possibility. Is there any place where these are documented? Searching
>> for ODBC options yields info on connection options, but none on
>> behavior that I could find.
>>  
> I know that there are a couple of options that affect the
> representation of Booleans in the odbc driver.  I'm not sure it will
> do what you need though.
>
> However, here is the official documentation: (hopefully it helpful)
>
> http://psqlodbc.projects.postgresql.org/
>
>
>
Yes there is an option to change bools to char

* *Data Type Options:* affects how some data types are mapped:
  o /Text as LongVarChar/: PostgreSQL TEXT type is mapped to
SQLLongVarchar, otherwise SQLVarchar.
  o /Unknowns as LongVarChar/: Unknown types (arrays, etc) are
mapped to SQLLongVarChar, otherwise SQLVarchar
  o /Bools as Char/: Bools are mapped to SQL_CHAR, otherwise to
SQL_BIT.

My memory is fuzzy but there are some additional settings in Access that 
allows data type mapping...



All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] workaround for missing ROWNUM feature with the help of GUC variables

2010-08-04 Thread Andreas Joseph Krogh 

On 08/04/2010 12:35 PM, Marc Mamin wrote:


Hello,

here my two pence on this recurring thema.

(just a workaround)




I don't understand what you mean by missing ROWNUM feature, PG got this 
with windows-functions in 8.4:

http://www.postgresql.org/docs/8.4/interactive/functions-window.html

Example:
select username, row_number() over() from my_user_table;
  username   | row_number
-+
 admin   |  1
 everyone|  2

--
Andreas Joseph Krogh
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

[SQL] Delete rules

2010-08-04 Thread Little, Douglas 
Hi,
We'd like to give our applications truncate capability, but that would also 
give them drop table permission, which we don't want them to have.
So, we created a truncate function that uses the definers security context.

The issue is that we now have 1000's of programs to change to use the function. 
 A task the developers don't want to do.

So,  we're investigating using a DELETE rule.  I'd like to be able to rewrite   
an unqualified delete into a the call to the truncate function otherwise, leave 
it alone.

But I don't know how to access the entire sql statement for interrogation.
Is there a way I can see what the statement is that's executing and triggering 
the rule?

Thanks


Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
[email protected]
 [cid:[email protected]]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [SQL] What does PostgreSQL do when time goes backward?

2010-08-04 Thread Frank Bax 

John Hasler wrote:

Frank writes:

My ntp client changes clock (by small amount) at any time:
Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s


Ken writes:

These do seem to be larger values than you might expect from a clock
conditioned with ntpd.  Is it a VM or is there something going on that
would stop or suspend your system?


There is certainly something wrong there.



System is not a VM; it runs 24/7 hosting for a few simple domains.  It 
runs OpenBSD, not Linux.  I believe OpenBSD has it's own ntpd 
implementation.  I read once that the amount of time mentioned is how 
much time clock needs to be changed, not how much the clock will 
actually be changed.  I know nothing about ntpd internals; thanks for 
letting me hijack this thread.  The log messages may be out-of-context, 
since I excluded frequency changes.  Still something wrong?


Jul 25 00:13:25 bax ntpd[10269]: adjusting local clock by -0.038453s
Jul 25 00:42:25 bax ntpd[10269]: adjusting clock frequency by -10.296223 
to -20.774345ppm

Jul 25 02:38:31 bax ntpd[10269]: adjusting local clock by 0.039182s
Jul 25 04:56:29 bax ntpd[10269]: adjusting clock frequency by 3.332808 
to -17.441537ppm

Jul 25 05:29:38 bax ntpd[10269]: adjusting local clock by 0.098724s
Jul 25 05:31:43 bax ntpd[10269]: adjusting local clock by 0.038991s
Jul 25 06:13:38 bax ntpd[10269]: adjusting local clock by -0.037131s
Jul 25 08:26:12 bax ntpd[10269]: adjusting clock frequency by -5.876526 
to -23.318063ppm
Jul 25 11:07:08 bax ntpd[10269]: adjusting clock frequency by 16.235236 
to -7.082827ppm

Jul 25 15:01:52 bax ntpd[10269]: adjusting local clock by -0.112429s
Jul 25 15:38:06 bax ntpd[10269]: adjusting clock frequency by -9.112840 
to -16.195667ppm
Jul 25 20:42:03 bax ntpd[10269]: adjusting clock frequency by 1.838351 
to -14.357316ppm
Jul 26 05:57:41 bax ntpd[10269]: adjusting clock frequency by -0.485347 
to -14.842663ppm
Jul 26 15:48:01 bax ntpd[10269]: adjusting clock frequency by -0.130635 
to -14.973298ppm
Jul 26 23:51:48 bax ntpd[10269]: adjusting clock frequency by -0.632588 
to -15.605885ppm
Jul 27 09:14:44 bax ntpd[10269]: adjusting clock frequency by 0.761208 
to -14.844677ppm

Jul 27 11:33:29 bax ntpd[5857]: adjusting local clock by 0.332560s
Jul 27 11:57:09 bax ntpd[5857]: adjusting clock frequency by -0.180636 
to -15.025316ppm
Jul 27 12:16:16 bax ntpd[5857]: adjusting clock frequency by 0.153771 to 
-14.871545ppm
Jul 27 12:38:36 bax ntpd[5857]: adjusting clock frequency by -0.314905 
to -15.186450ppm
Jul 27 22:30:47 bax ntpd[5857]: adjusting clock frequency by -0.058928 
to -15.245378ppm
Jul 28 22:00:57 bax ntpd[5857]: adjusting clock frequency by 0.508728 to 
-14.750045ppm
Jul 29 07:09:05 bax ntpd[5857]: adjusting clock frequency by 0.056698 to 
-14.693347ppm
Jul 29 21:25:01 bax ntpd[5857]: adjusting clock frequency by -0.233626 
to -14.926974ppm
Jul 30 06:28:33 bax ntpd[5857]: adjusting clock frequency by -0.255362 
to -15.182336ppm
Jul 30 15:45:17 bax ntpd[5857]: adjusting clock frequency by 0.291147 to 
-14.891189ppm
Jul 31 11:57:51 bax ntpd[5857]: adjusting clock frequency by 0.198529 to 
-14.725782ppm
Aug  1 08:47:12 bax ntpd[5857]: adjusting clock frequency by -0.063213 
to -14.806622ppm
Aug  2 12:19:10 bax ntpd[5857]: adjusting clock frequency by 0.096080 to 
-14.759072ppm
Aug  3 01:30:19 bax ntpd[5857]: adjusting clock frequency by -0.353416 
to -15.112489ppm
Aug  3 08:10:34 bax ntpd[5857]: adjusting clock frequency by 0.251130 to 
-14.861359ppm
Aug  3 20:29:11 bax ntpd[5857]: adjusting clock frequency by -0.105335 
to -14.966694ppm

Aug  4 07:22:22 bax ntpd[27189]: adjusting local clock by -0.097095s
Aug  4 07:49:26 bax ntpd[27189]: adjusting clock frequency by -2.970136 
to -17.898726ppm
Aug  4 08:04:04 bax ntpd[27189]: adjusting clock frequency by 2.495076 
to -15.403651ppm


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Richard Broersma 
On Wed, Aug 4, 2010 at 1:24 PM, Justin Graf  wrote:

> My memory is fuzzy but there are some additional settings in Access that
> allows data type mapping...

My experience is that PostgreSQL Integer types work the best for
MS-Access bit datatype considering the fact that in Access -1 = true.

I know that there is a setting in the ODBC driver for true = -1 but it
doesn't work well.  I also remember that filters didn't work on mapped
boolean columns.

For example:

2010-06-08 14:39:43 PDTERROR:  invalid input syntax for type boolean:
"-1" at character 49
2010-06-08 14:39:43 PDTSTATEMENT:
BEGIN;
UPDATE "public"."structures"
SET "scoped"=E'-1'
WHERE "buildingfunction" = E'CRANE OPERATOR STATION'
AND "xmin" = 20497
-06-08 14:39:43 PDTLOG:  duration: 0.000 ms  statement: ROLLBACK

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Delete rules

2010-08-04 Thread Richard Broersma 
On Wed, Aug 4, 2010 at 1:25 PM, Little, Douglas
wrote:


> But I don’t know how to access the entire sql statement for
> interrogation.   Is there a way I can see what the statement is that’s
> executing and triggering the rule?
>

I've found complex and simple rules pretty hard to get correct.  They
sometimes do unexpected things.

It looks like the CREATE Trigger  FOR EACH STATEMENT is better suited to do
what you want:

"In addition, triggers may be defined to fire for a TRUNCATE, though only FOR
EACH STATEMENT."

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Peter Koczan 
On Wed, Aug 4, 2010 at 4:40 PM, Richard Broersma
 wrote:
> On Wed, Aug 4, 2010 at 1:24 PM, Justin Graf  wrote:
>
>> My memory is fuzzy but there are some additional settings in Access that
>> allows data type mapping...
>
> My experience is that PostgreSQL Integer types work the best for
> MS-Access bit datatype considering the fact that in Access -1 = true.
>
> I know that there is a setting in the ODBC driver for true = -1 but it
> doesn't work well.  I also remember that filters didn't work on mapped
> boolean columns.

Yep, that's the stumbling block we're running into. ODBC and these
fields' assumptions of true/false are at odds. I'm trying a few other
things with casts in the meantime to see if they'll work.

Does anyone know if another product, like OpenOffice Base with its
native postgres driver, does any better?

Peter

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Domains, casts, and MS Access

2010-08-04 Thread Richard Broersma 
On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan  wrote:

> Yep, that's the stumbling block we're running into. ODBC and these
> fields' assumptions of true/false are at odds. I'm trying a few other
> things with casts in the meantime to see if they'll work.

Well there is a solution that I've been toying around with.  In
PostgreSQL, there are many data-types that cannot be expressed
directly in an MS-Access Linked table.  For example, composite types,
arrays, range types, hstores, postgis types et.al.   However, most of
these types can be decomposed in to base types that can be express in
linked tables.

The key is using update-able views to decompose the data for Access
and re-assemble it before it transmitted back to the base table.  The
same can be done for boolean datatype.


> Does anyone know if another product, like OpenOffice Base with its
> native postgres driver, does any better?

>From my limited experience, I believe is does do better.  The
following blogs as a few entries about using Base:

http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql