[GENERAL] Re: Exists subquery in an update ignores the effects of the update itself

[David G Johnston]

Jeff Janes wrote
> I want to update some data in unique column.  Some of the updates would
> conflict if applied to eligible rows, and for now I want to skip those
> updates, applying only one of a set of conflicting ones.  I can use a not
> exists subquery to detect when the new value would conflict with an
> existing one, but it does not see the "existing" value if that value was
> itself the result of an update in the same statement.
> 
> See the contrived example:
> 
> 
> create table foo (x text unique);
> insert into foo values ('aac'),('aad'),('aae');
> 
> update foo a set x=substr(x,1,2) where x!=substr(x,1,2)
>and not exists (select 1 from foo b where b.x=substr(a.x,1,2));
> 
> ERROR:  duplicate key value violates unique constraint "foo_x_key"
> DETAIL:  Key (x)=(aa) already exists.
> 
> Is there a way to phrase this in a single statement so it will do what I
> want, updating one row and leaving two unchanged?
> 
> Or do I have to mess around with a temp table?
> 
> Thanks,
> 
> Jeff

You can probably solve the larger problem using deferred constraints.

http://www.postgresql.org/docs/devel/static/sql-set-constraints.html

Your stated problem can probably be solved using a CTE and a window
function.  Write the cte query so that your duplicate-inducing values form a
partition and use row_number and order by to pick one of the items in each
partition as the first and only record to update.  Then use that cte (or
subquery) to pick the rows on the table to actually apply the update to.

Basically an inline temp table.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Exists-subquery-in-an-update-ignores-the-effects-of-the-update-itself-tp5817885p5817890.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Exists subquery in an update ignores the effects of the update itself

[Jeff Janes]

I want to update some data in unique column.  Some of the updates would
conflict if applied to eligible rows, and for now I want to skip those
updates, applying only one of a set of conflicting ones.  I can use a not
exists subquery to detect when the new value would conflict with an
existing one, but it does not see the "existing" value if that value was
itself the result of an update in the same statement.

See the contrived example:


create table foo (x text unique);
insert into foo values ('aac'),('aad'),('aae');

update foo a set x=substr(x,1,2) where x!=substr(x,1,2)
   and not exists (select 1 from foo b where b.x=substr(a.x,1,2));

ERROR:  duplicate key value violates unique constraint "foo_x_key"
DETAIL:  Key (x)=(aa) already exists.

Is there a way to phrase this in a single statement so it will do what I
want, updating one row and leaving two unchanged?

Or do I have to mess around with a temp table?

Thanks,

Jeff

[GENERAL] CONCAT function

[Vinayak]

Hello,

The pg_catalog.concat() is defined as STABLE function.

As per my understanding a STABLE function cannot modify the database and is
guaranteed to return the same results given the same arguments for all rows
within a single statement.
Example:
current_timestamp family of functions qualify as stable, since their values
do not change within a transaction.

An IMMUTABLE function cannot modify the database and is guaranteed to return
the same results given the same arguments forever.

why was STABLE preferred for concat() over IMMUTABLE?




-
Regards,
Vinayak,

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/CONCAT-function-tp5817884.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to pass tablename to a function

[alecinvan]

Hi, All

I like to pass the tablename to function but not using execute clause, here
is my script

CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text,
IN jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN
stopid bigint)
  RETURNS TABLE(events bigint, errors bigint[]) AS
$BODY$
BEGIN

RETURN QUERY
WITH
 unpacking (raw_id, time, userid, eventtype, pagename, userhost,
application, status, error)
 AS (
select 
  id as raw_id, 
  (up).time, 
  (up).userid,
  coalesce((up).eventtype, ''),
  coalesce((up).pagename, ''),
  (up).userhostaddress as userhost,
  coalesce((up).application, ''),
  (up).status, 
  (up).error
 from(
  select id, 
 functions.python_events_unpack(event_object) up
  from  _tbl 
  where id between startid and stopid
 ) a 
 where (up).userid is not NULL 
  )


I want to pass the _tbl to the select query integrated in the unpacking(),
how can I make it?

thanks

Alec






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-tablename-to-a-function-tp5817861.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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 pass tablename to a function

[David G Johnston]

alecinvan wrote
> I like to pass the tablename to function but not using execute clause,
> here is my script
> 
> [...]
> 
> I want to pass the _tbl to the select query integrated in the unpacking(),
> how can I make it?

There is no way to perform a query with an unknown, at design time,
identifier without using EXECUTE.

The recommended way to do this is to use "format()" and dollar-quoting -
v9.1+ required:

v_qry := format(

$qry$
WITH [...]
SELECT id, func(...) FROM %I WHERE id [...]
$qry$

, _tbl

);

RETURN QUERY EXECUTE v_qry;

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-tablename-to-a-function-tp5817864p5817871.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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 pass tablename to a function

[Adrian Klaver]

On 09/04/2014 04:42 PM, A L. wrote:

Hi, All

I like to pass the tablename to function but not using execute clause,
here is my script

CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl
text, IN jobid bigint, IN jobtime timestamp with time zone, IN startid
bigint, IN stopid bigint)
   RETURNS TABLE(events bigint, errors bigint[]) AS
$BODY$
BEGIN

RETURN QUERY
WITH
  unpacking (raw_id, time, userid, eventtype, pagename, userhost,
application, status, error)
  AS (
 select
   id as raw_id,
   (up).time,
   (up).userid,
   coalesce((up).eventtype, ''),
   coalesce((up).pagename, ''),
   (up).userhostaddress as userhost,
   coalesce((up).application, ''),
   (up).status,
   (up).error
  from(
   select id,
  functions.python_events_unpack(event_object) up
   from  _tbl
   where id between startid and stopid
  ) a
  where (up).userid is not NULL
   )


I want to pass the _tbl to the select query integrated in the
unpacking(), how can I make it?


Assuming you are using plpgsql, see here:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

or use a language like plpythonu.



thanks

Alec



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to pass tablename to a function

[A L .]

Hi, All


I like to pass the tablename to function but not using execute clause, here is 
my script


CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN
 _tbl text, IN jobid bigint, IN jobtime timestamp with time zone, IN 
startid bigint, IN stopid bigint)

  RETURNS TABLE(events bigint, errors bigint[]) AS

$BODY$

BEGIN


RETURN QUERY

WITH

 unpacking (raw_id, time, userid, eventtype, pagename, userhost, 
application, status, error)

 AS (

select 

  id as raw_id, 

  (up).time, 

  (up).userid,

  coalesce((up).eventtype, ''),

  coalesce((up).pagename, ''),

  (up).userhostaddress as userhost,

  coalesce((up).application, ''),

  (up).status, 

  (up).error

 from(

  select id, 

 functions.python_events_unpack(event_object) up

  from  _tbl 

  where id between startid and stopid

 ) a 

 where (up).userid is not NULL 

  )



I want to pass the _tbl to the select query integrated in the unpacking(), how 
can I make it?


thanks


Alec
  

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Adrian Klaver]

On 09/04/2014 08:03 AM, Alanoly Andrews wrote:

Thanks, Adrian for the response.

Yes, we are using the "large object" as per the specifications in the
special "lo" module that we installed on the backend server. The table
is created using the "lo" datatype for the image field and the table is
being regularly used for insertions and retrievals using utilities other
than ones using odbc. The odbc connection, too, works well on the table,
as long as the table data is not reorganized after insertion(s).

Here are three pages of our postgres odbc driver settings. Sorry, they
are screen captures,  not plain text!


Hmm, to me at least nothing seems out of place. Though to be honest I am 
not sure what 'Bytea as LO' does in this situation.


Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly 
generic and covers a lot of different error sources.


So, is there any more specific error information in either the Postgres 
or ODBC logs?




Alanoly.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Adrian Klaver]

On 09/04/2014 07:15 AM, David G Johnston wrote:

Adrian Klaver-4 wrote

On 09/04/2014 05:56 AM, Alanoly Andrews wrote:

Hi Adrian,

Thanks for that explanation of how the "relfilenode" changes after a
table reorganization. It is not surprising that this happens because the
table rows are being physically moved from one location to another. But
such changes at the backend should be transparent to the end user. The VB
code at the client side runs a simple sql like "select image from
image_table where image_key=somevalue". There is no reference to
postgres-specific internal variables like "oid" and "relfilenode". I do
not know the inner workings of the postgres odbc driver; but I would be
surprised if it works at the granularity of "oid" and "relfilenode" and
that it would store the actual physical values of relfilenode (which
would keep changing after every table reload, reorg etc.).


In addition to what David mentioned, some more detail on what you are
calling a 'large object'.  In Postgres there is not really a 'large
object' type(though I am guilty of saying there is), instead there is a
way of working with large objects outside of the bytea type. Now in the
ODBC FAQ there is a recipe for creating a 'lo' type:

http://psqlodbc.projects.pgfoundry.org/faq.html#4.4

Is this what you are using?

If not how are you working with the large objects?

Also given that you are working with the Postgres ODBC driver, you might
want to break the cross post rule and ask this question on the psql-odbc
list:

http://www.postgresql.org/list/pgsql-odbc/

In any case your ODBC settings would be helpful.


I suspect the OP is referring to the contrib module/extension:

http://www.postgresql.org/docs/9.1/interactive/lo.html


Aaah, did not know that existed, thanks.



David J.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Employee modeling question

[Robin]

Robin St.Clair
On 04/09/2014 20:44, Nelson Green wrote:
On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil 
mailto:franc...@teksol.info>> wrote:


Hello Nelson,

Le 2014-09-04 à 10:39, Nelson Green mailto:nelsongree...@gmail.com>> a écrit :

> Good morning,
>
> Hopefully this is the correct place to ask this type of question.
>
> I am in the early stages of designing a system to track employee
> information, including some aspects of their payroll, one of
which is
> the source of the salary funds within the business. I need to make
> this generic enough to accommodate instances where an employee's
> salary is sourced from a department's budget, as well as those
> salaries of employees that are employed by companies too small to be
> departmentalized. So when an employee is employed by a
department, the
> department is the source of the employee's salary, and the
business is
> the department's parent entity. But when an employee is employed
by a
> business that has no departments, the business entity is the
source of
> the employee's salary, and the parent entity. I am struggling
with the
> correct logical implementation of this scenario.
>
> So basically I have a situation where an employee will most
likely be
> associated with a department within a business, but this can not be
> guaranteed, and I'm not 100% sure how to handle this. I am going to
> face the same problem with contracts where a department can
out-source
> a function, or a business can. I think there may even be instances
> where a business with departments may out-source functionality
that is
> not charged to a department.
>
> I could store the department and business attributes with the
> employee, but without proper constraints the referenced department
> could conceivably not correspond to the referenced business. Or I
> could ensure that all businesses have at least one department,
> defaulting to the business when the business has no department, but
> then I'd be storing duplicate data. The other alternative I've
come up
> with is an exclusive constraint where the employee instance can only
> reference a department or a business, but not both.
>
> None of these solutions seems ideal, although the exclusivity
solution
> seems like it would work the best, and I have had to create
> exclusivity constraints before. So, am I missing a more obvious
> solution, or am I even on track here?

I’ve found this « Universal Person and Organization Data Model »
very useful to understand complex questions like that:
http://www.tdan.com/view-articles/5014

Hope this helps,
François Beausoleil

Thanks François. This is pretty generic stuff, but my first reading 
has got me thinking that I should at least pick up some ideas from it. 
I will give it a go. And I've got a new web site that I've now known 
about before, so thanks for that as well.


Regards,
Nelson


I strongly suggest you read the writings of Joe Celko, he has been 
addressing this kind of problem for decades. Ultimately, it is very 
important not to confuse behaviour with entities (yes employment is 
behavioural)


Cheers

Re: [GENERAL] Employee modeling question

[Nelson Green]

On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil 
wrote:

> Hello Nelson,
>
> Le 2014-09-04 à 10:39, Nelson Green  a écrit :
>
> > Good morning,
> >
> > Hopefully this is the correct place to ask this type of question.
> >
> > I am in the early stages of designing a system to track employee
> > information, including some aspects of their payroll, one of which is
> > the source of the salary funds within the business. I need to make
> > this generic enough to accommodate instances where an employee's
> > salary is sourced from a department's budget, as well as those
> > salaries of employees that are employed by companies too small to be
> > departmentalized. So when an employee is employed by a department, the
> > department is the source of the employee's salary, and the business is
> > the department's parent entity. But when an employee is employed by a
> > business that has no departments, the business entity is the source of
> > the employee's salary, and the parent entity. I am struggling with the
> > correct logical implementation of this scenario.
> >
> > So basically I have a situation where an employee will most likely be
> > associated with a department within a business, but this can not be
> > guaranteed, and I'm not 100% sure how to handle this. I am going to
> > face the same problem with contracts where a department can out-source
> > a function, or a business can. I think there may even be instances
> > where a business with departments may out-source functionality that is
> > not charged to a department.
> >
> > I could store the department and business attributes with the
> > employee, but without proper constraints the referenced department
> > could conceivably not correspond to the referenced business. Or I
> > could ensure that all businesses have at least one department,
> > defaulting to the business when the business has no department, but
> > then I'd be storing duplicate data. The other alternative I've come up
> > with is an exclusive constraint where the employee instance can only
> > reference a department or a business, but not both.
> >
> > None of these solutions seems ideal, although the exclusivity solution
> > seems like it would work the best, and I have had to create
> > exclusivity constraints before. So, am I missing a more obvious
> > solution, or am I even on track here?
>
> I’ve found this « Universal Person and Organization Data Model » very
> useful to understand complex questions like that:
> http://www.tdan.com/view-articles/5014
>
> Hope this helps,
> François Beausoleil
>
>
Thanks François. This is pretty generic stuff, but my first reading has got
me thinking that I should at least pick up some ideas from it. I will give
it a go. And I've got a new web site that I've now known about before, so
thanks for that as well.

Regards,
Nelson

Re: [GENERAL] free RAM not being used for page cache

[Kevin Goess]

This is a super-interesting topic, thanks for all the info.

On Thu, Sep 4, 2014 at 7:44 AM, Shaun Thomas 
wrote:
>
> Check /proc/meminfo for a better breakdown of how the memory is being
> used. This should work:
>
> grep -A1 Active /proc/meminfo
>
> I suspect your inactive file cache is larger than the active set,
> suggesting an overly aggressive memory manager.


$ grep -A1 Active /proc/meminfo
Active: 34393512 kB
Inactive:   20765832 kB
Active(anon):   13761028 kB
Inactive(anon):   890688 kB
Active(file):   20632484 kB
Inactive(file): 19875144 kB

The inactive set isn't larger than the active set, they're about even, but
I'm still reading that as the memory manager being aggressive in marking
pages as inactive, is that what it says to you too?

Interestingly, I just looked at the memory graph for our standby backup
database, and while it *normally* uses all the available RAM as the page
cache, which is what I'd expect to see, when it was the active database for
a time in April and May, the page cache size was reduced by about the same
margin. So it's the act of running an active postgres instance that causes
the phenomenon.

http://s76.photobucket.com/user/kgoesspb/media/db2-mem-historic.png.html



-- 
Kevin M. Goess
Software Engineer
Berkeley Electronic Press
kgo...@bepress.com

510-665-1200 x179
www.bepress.com

bepress: sustainable scholarly publishing

Re: [GENERAL] GiST index question

[Eric Fleming]

Thank you both, I will look into alternative data types. I don’t think ltree 
will work for my purposes but I am going to try out some others that might; 
like cube.
—
Eric Fleming

On Thu, Sep 4, 2014 at 3:42 AM, Giuseppe Broccolo
 wrote:

> Hi Eric,
> As Michael said, path data type does not support for gist operators.
> Anyway, you could redefine data type using 'ltree' instead of 'path'. Take
> a look on the following link:
> http://www.postgresql.org/docs/9.1/static/ltree.html
> Try to understand if this could be fine for you.
> Cheers,
> Giuseppe.
> 2014-09-04 6:31 GMT+02:00 Michael Paquier :
>> On Thu, Sep 4, 2014 at 8:35 AM, Eric Fleming  wrote:
>> > I have a table that I have defined as:
>> >
>> > CREATE TABLE test (
>> > "id" SERIAL PRIMARY KEY,
>> > "first_path" path NOT NULL,
>> > "second_path" path NOT NULL
>> > );
>> >
>> > I am attempting to create a GiST index on the two "path" columns using:
>> >
>> > CREATE INDEX  "idx_test_first_path" ON test USING gist(first_path);
>> > CREATE INDEX  "idx_test_second_path" ON test USING gist(second_path);
>> >
>> > I am getting this error:
>> >
>> > Error : ERROR:  data type path has no default operator class for access
>> > method "gist"
>> >
>> > Is it that I am not allowed to create an index on the path data type or
>> do I
>> > need to enable an extension for this to work? Thanks in advance for your
>> > help.
>> In-core contrib modules (and core) do not have yet support for gist
>> operator classes for the datatype path as far as I recall.
>> Regards,
>> --
>> Michael
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
> -- 
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Alanoly Andrews]

Thanks, Adrian for the response.



Yes, we are using the "large object" as per the specifications in the special 
"lo" module that we installed on the backend server. The table is created using 
the "lo" datatype for the image field and the table is being regularly used for 
insertions and retrievals using utilities other than ones using odbc. The odbc 
connection, too, works well on the table, as long as the table data is not 
reorganized after insertion(s).



Here are three pages of our postgres odbc driver settings. Sorry, they are 
screen captures,  not plain text!



[cid:image001.png@01CFC82F.D8DB69F0]



[cid:image002.png@01CFC82F.D8DB69F0]



[cid:image003.png@01CFC82F.D8DB69F0]





Alanoly.



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, September 04, 2014 9:39 AM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-ad...@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB 
and odbc, after a table reorganization.



On 09/04/2014 05:56 AM, Alanoly Andrews wrote:

> Hi Adrian,

>

> Thanks for that explanation of how the "relfilenode" changes after a table 
> reorganization. It is not surprising that this happens because the table rows 
> are being physically moved from one location to another. But such changes at 
> the backend should be transparent to the end user. The VB code at the client 
> side runs a simple sql like "select image from image_table where 
> image_key=somevalue". There is no reference to postgres-specific internal 
> variables like "oid" and "relfilenode". I do not know the inner workings of 
> the postgres odbc driver; but I would be surprised if it works at the 
> granularity of "oid" and "relfilenode" and that it would store the actual 
> physical values of relfilenode (which would keep changing after every table 
> reload, reorg etc.).



In addition to what David mentioned, some more detail on what you are calling a 
'large object'.  In Postgres there is not really a 'large object' type(though I 
am guilty of saying there is), instead there is a way of working with large 
objects outside of the bytea type. Now in the ODBC FAQ there is a recipe for 
creating a 'lo' type:



http://psqlodbc.projects.pgfoundry.org/faq.html#4.4



Is this what you are using?



If not how are you working with the large objects?



Also given that you are working with the Postgres ODBC driver, you might want 
to break the cross post rule and ask this question on the psql-odbc

list:



http://www.postgresql.org/list/pgsql-odbc/



In any case your ODBC settings would be helpful.



>

> Alanoly.

>





--

Adrian Klaver

adrian.kla...@aklaver.com



If you no longer wish to receive any of our emails, click on 
UNSUBSCRIBE. This 
e-mail may be privileged and/or confidential, and the sender does not waive any 
related rights and obligations. Any distribution, use or copying of this e-mail 
or the information it contains by other than an intended recipient is 
unauthorized. If you received this e-mail in error, please advise me (by return 
e-mail or otherwise) immediately.


Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur 
DÉSABONNEMENT. Ce 
courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et 
obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.

Re: [GENERAL] || operator

[Brett Mc Bride]

On Wed, 2014-09-03 at 21:27 -0700, Vinayak wrote:
> Hello Pavel,
>
> Thank you for reply.
> >postgres=# select 'abc   '::char(7) || 'dbe   '::char(6);
> >?column?
> >
> > *abcabc*
> >(1 row)
> but it gives the result "abcabc". It should be "abcdbe".
>
>
I believe there was a typo in the function, try this one
:
postgres=# create or replace function concat_character(character,
character) returns text as $$ select concat($1,$2)$$ language sql;
>
>
>
> -
> Regards,
> Vinayak,
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817674.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


Important Notice: The contents of this email are intended solely for the named 
addressee and are confidential; any unauthorised use, reproduction or storage 
of the contents is expressly prohibited. If you have received this email in 
error, please delete it and any attachments immediately and advise the sender 
by return email or telephone.

Deakin University does not warrant that this email and any attachments are 
error or virus free.

-- 
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] Employee modeling question

[François Beausoleil]

Hello Nelson,

Le 2014-09-04 à 10:39, Nelson Green  a écrit :

> Good morning,
> 
> Hopefully this is the correct place to ask this type of question.
> 
> I am in the early stages of designing a system to track employee
> information, including some aspects of their payroll, one of which is
> the source of the salary funds within the business. I need to make
> this generic enough to accommodate instances where an employee's
> salary is sourced from a department's budget, as well as those
> salaries of employees that are employed by companies too small to be
> departmentalized. So when an employee is employed by a department, the
> department is the source of the employee's salary, and the business is
> the department's parent entity. But when an employee is employed by a
> business that has no departments, the business entity is the source of
> the employee's salary, and the parent entity. I am struggling with the
> correct logical implementation of this scenario.
> 
> So basically I have a situation where an employee will most likely be
> associated with a department within a business, but this can not be
> guaranteed, and I'm not 100% sure how to handle this. I am going to
> face the same problem with contracts where a department can out-source
> a function, or a business can. I think there may even be instances
> where a business with departments may out-source functionality that is
> not charged to a department.
> 
> I could store the department and business attributes with the
> employee, but without proper constraints the referenced department
> could conceivably not correspond to the referenced business. Or I
> could ensure that all businesses have at least one department,
> defaulting to the business when the business has no department, but
> then I'd be storing duplicate data. The other alternative I've come up
> with is an exclusive constraint where the employee instance can only
> reference a department or a business, but not both.
> 
> None of these solutions seems ideal, although the exclusivity solution
> seems like it would work the best, and I have had to create
> exclusivity constraints before. So, am I missing a more obvious
> solution, or am I even on track here?

I’ve found this « Universal Person and Organization Data Model » very useful to 
understand complex questions like that: http://www.tdan.com/view-articles/5014

Hope this helps,
François Beausoleil



-- 
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] free RAM not being used for page cache

[Shaun Thomas]

On 09/03/2014 07:17 PM, Kevin Goess wrote:


Debian squeeze, still on 2.6.32.


Interesting. Unfortunately that kernel suffers from the newer task 
scheduler they added to 3.2, and I doubt much of the fixes have been 
back-ported. I don't know if that affects the memory handling, but it might.



Darn, really? I just learned about the "mysql swap insanity" problem and
noticed that all the free memory is concentrated on one of the two nodes.

$ numactl --hardware
available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6
node 0 size: 32768 MB
node 0 free: 9105 MB
node 1 cpus: 1 3 5 7
node 1 size: 32755 MB
node 1 free: 259 MB


And that's the kind of behavior we were seeing until we upgraded to 3.8. 
A 8GB gap between your nodes is definitely bad, but it's not the same 
thing they described in the MySQL swap insanity posts. MySQL has a much 
bigger internal cache than we do, so expects a good proportion of system 
memory. It's not uncommon for dedicated MySQL systems to have more than 
75% of system memory dedicated to database use. Without NUMA 
interleaving, that's a recipe for a broken system.



$ free
  total   used   free sharedbuffers cached
Mem:  66099280   565658049533476  0  11548   51788624


And again, this is what we started seeing with 3.2 when we upgraded 
initially. Unfortunately it looks like at least one of the bad memory 
aging patches got backported to the kernel you're using. If everything 
were working properly, that excess 9GB would be in your cache.


Check /proc/meminfo for a better breakdown of how the memory is being 
used. This should work:


grep -A1 Active /proc/meminfo

I suspect your inactive file cache is larger than the active set, 
suggesting an overly aggressive memory manager.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Employee modeling question

[Nelson Green]

Good morning,

Hopefully this is the correct place to ask this type of question.

I am in the early stages of designing a system to track employee
information, including some aspects of their payroll, one of which is
the source of the salary funds within the business. I need to make
this generic enough to accommodate instances where an employee's
salary is sourced from a department's budget, as well as those
salaries of employees that are employed by companies too small to be
departmentalized. So when an employee is employed by a department, the
department is the source of the employee's salary, and the business is
the department's parent entity. But when an employee is employed by a
business that has no departments, the business entity is the source of
the employee's salary, and the parent entity. I am struggling with the
correct logical implementation of this scenario.

So basically I have a situation where an employee will most likely be
associated with a department within a business, but this can not be
guaranteed, and I'm not 100% sure how to handle this. I am going to
face the same problem with contracts where a department can out-source
a function, or a business can. I think there may even be instances
where a business with departments may out-source functionality that is
not charged to a department.

I could store the department and business attributes with the
employee, but without proper constraints the referenced department
could conceivably not correspond to the referenced business. Or I
could ensure that all businesses have at least one department,
defaulting to the business when the business has no department, but
then I'd be storing duplicate data. The other alternative I've come up
with is an exclusive constraint where the employee instance can only
reference a department or a business, but not both.

None of these solutions seems ideal, although the exclusivity solution
seems like it would work the best, and I have had to create
exclusivity constraints before. So, am I missing a more obvious
solution, or am I even on track here?

Thanks in advance and regards,
Nelson


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[David G Johnston]

Adrian Klaver-4 wrote
> On 09/04/2014 05:56 AM, Alanoly Andrews wrote:
>> Hi Adrian,
>>
>> Thanks for that explanation of how the "relfilenode" changes after a
>> table reorganization. It is not surprising that this happens because the
>> table rows are being physically moved from one location to another. But
>> such changes at the backend should be transparent to the end user. The VB
>> code at the client side runs a simple sql like "select image from
>> image_table where image_key=somevalue". There is no reference to
>> postgres-specific internal variables like "oid" and "relfilenode". I do
>> not know the inner workings of the postgres odbc driver; but I would be
>> surprised if it works at the granularity of "oid" and "relfilenode" and
>> that it would store the actual physical values of relfilenode (which
>> would keep changing after every table reload, reorg etc.).
> 
> In addition to what David mentioned, some more detail on what you are 
> calling a 'large object'.  In Postgres there is not really a 'large 
> object' type(though I am guilty of saying there is), instead there is a 
> way of working with large objects outside of the bytea type. Now in the 
> ODBC FAQ there is a recipe for creating a 'lo' type:
> 
> http://psqlodbc.projects.pgfoundry.org/faq.html#4.4
> 
> Is this what you are using?
> 
> If not how are you working with the large objects?
> 
> Also given that you are working with the Postgres ODBC driver, you might 
> want to break the cross post rule and ask this question on the psql-odbc 
> list:
> 
> http://www.postgresql.org/list/pgsql-odbc/
> 
> In any case your ODBC settings would be helpful.

I suspect the OP is referring to the contrib module/extension:

http://www.postgresql.org/docs/9.1/interactive/lo.html

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817761.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Adrian Klaver]

On 09/04/2014 05:56 AM, Alanoly Andrews wrote:

Hi Adrian,

Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens 
because the table rows are being physically moved from one location to another. But such changes at the backend should be transparent to 
the end user. The VB code at the client side runs a simple sql like "select image from image_table where image_key=somevalue". 
There is no reference to postgres-specific internal variables like "oid" and "relfilenode". I do not know the inner 
workings of the postgres odbc driver; but I would be surprised if it works at the granularity of "oid" and 
"relfilenode" and that it would store the actual physical values of relfilenode (which would keep changing after every table 
reload, reorg etc.).


In addition to what David mentioned, some more detail on what you are 
calling a 'large object'.  In Postgres there is not really a 'large 
object' type(though I am guilty of saying there is), instead there is a 
way of working with large objects outside of the bytea type. Now in the 
ODBC FAQ there is a recipe for creating a 'lo' type:


http://psqlodbc.projects.pgfoundry.org/faq.html#4.4

Is this what you are using?

If not how are you working with the large objects?

Also given that you are working with the Postgres ODBC driver, you might 
want to break the cross post rule and ask this question on the psql-odbc 
list:


http://www.postgresql.org/list/pgsql-odbc/

In any case your ODBC settings would be helpful.



Alanoly.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[David G Johnston]

You are going to have to help us by providing server (and any other) logs
with complete error messages and VB/ODBC code, with corresponding schema,
that will reliably reproduce the problem.

Note that since you are on an ancient 9.1 release it is possible that, if
this is indeed a bug, this has already been fixed in one of the 10
subsequent releases.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817743.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Alanoly Andrews]

Hi Adrian,

Thanks for that explanation of how the "relfilenode" changes after a table 
reorganization. It is not surprising that this happens because the table rows 
are being physically moved from one location to another. But such changes at 
the backend should be transparent to the end user. The VB code at the client 
side runs a simple sql like "select image from image_table where 
image_key=somevalue". There is no reference to postgres-specific internal 
variables like "oid" and "relfilenode". I do not know the inner workings of the 
postgres odbc driver; but I would be surprised if it works at the granularity 
of "oid" and "relfilenode" and that it would store the actual physical values 
of relfilenode (which would keep changing after every table reload, reorg etc.).

Alanoly.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, September 03, 2014 3:10 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-ad...@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB 
and odbc, after a table reorganization.

On 09/03/2014 09:26 AM, Alanoly Andrews wrote:
> *Hi Craig,*
>
> **
>
> *I’m reloading into the very same database. *
>
> *If I insert a new row into the table (through VB/ODBC), I’m able to
> retrieve it (again from VB). But if after inserting the new row, I do
> a reorg of the table (eg. with a “cluster table”), I’m no longer able
> to retrieve that same row. In short, as long as the newly inserted
> rows are not “re-written” in some way, they can be retrieved through
> VB/ODBC. *

To follow up on my previous post with regards to OIDs. That may have been a 
wrong fork. A little testing:

test=# CREATE TABLE image (
 nametext,
 raster  oid
);

test=# create index img_idx on image (raster);

test=# INSERT INTO image (name, raster)
 VALUES ('another beautiful image', 
lo_import('/home/aklaver/Pictures/IMG_0359JPG'));

test=# INSERT INTO image (name, raster)
 VALUES ('another beautiful image', 
lo_import('/home/aklaver/Pictures/IMG_0360.JPG'));

test=# INSERT INTO image (name, raster)
 VALUES ('another beautiful image', 
lo_import('/home/aklaver/Pictures/IMG_0361.JPG'));

test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
+-
  532144 |  532175

test=# cluster image using img_idx;
CLUSTER
test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
+-
  532144 |  532182

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use --oids 
with pg_dump).

So to revise my previous statement, my guess is your VB/ODBC code is using the 
relfilenode value to refer to the table and when that changes it cannot find it 
any more.

>
> **
>
> *Alanoly.*
>



--
Adrian Klaver
adrian.kla...@aklaver.com



If you no longer wish to receive any of our emails, click on 
UNSUBSCRIBE. This 
e-mail may be privileged and/or confidential, and the sender does not waive any 
related rights and obligations. Any distribution, use or copying of this e-mail 
or the information it contains by other than an intended recipient is 
unauthorized. If you received this e-mail in error, please advise me (by return 
e-mail or otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur 
DÉSABONNEMENT. Ce 
courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et 
obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.

-- 
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] || operator

[Pavel Stehule]

2014-09-04 11:13 GMT+02:00 Vinayak :

> Hi,
>
> The || operator with arguments (character,character) works fine and even ||
> operator(character,varchar) also works fine.
> but || operator is not working as expected with arguments character data
> type and any other data type like integer,smallint,date,text.
> Example:
> postgres=# select 'ab'::char(10) || 4::int;
>  ?column?
> --
>  ab4
> (1 row)
> postgres=# select 'ab'::char(10) || 'a'::text;
>   ?column?
> -
>  aba
> (1 row)
>
> so I have created || operator with argument character and anyelement.
> Example:
> create or replace function concat_character(character, anyelement) returns
> text as $$ select concat($1,$2)$$ language sql;
> create operator || (procedure = concat_character, leftarg = character,
> rightarg = anyelement);
> it works fine with argument of type int,smallint,bigint,date etc.
> but its not working with text and varchar data type.
> Example:
> postgres=# select 'ab'::char(10) || 4::int;
>   ?column?
> -
>  ab4
> (1 row)
>
> postgres=# select 'ab'::char(10) || 'b'::text;
>  ?column?
> --
>  abb
> (1 row)
>

text is more general -- it it does cast to text - there is not || operator
for leftarg character and righarg text


>
> postgres=# select 'ab'::char(10) || 'b'::varchar(5);
> ERROR:  operator is not unique: character || character varying
> LINE 1: select 'ab'::char(10) || 'b'::varchar(5);
>   ^
> HINT:  Could not choose a best candidate operator. You might need to add
> explicit type casts.
> Thought?
>
>
Not too much - it is limit of Postgres type system :(

Pavel


>
> -
> Regards,
> Vinayak,
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817712.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> 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] || operator

[Vinayak]

Hi,

The || operator with arguments (character,character) works fine and even ||
operator(character,varchar) also works fine.
but || operator is not working as expected with arguments character data
type and any other data type like integer,smallint,date,text.
Example:
postgres=# select 'ab'::char(10) || 4::int;
 ?column? 
--
 ab4
(1 row)
postgres=# select 'ab'::char(10) || 'a'::text;
  ?column?   
-
 aba
(1 row)

so I have created || operator with argument character and anyelement.
Example:
create or replace function concat_character(character, anyelement) returns
text as $$ select concat($1,$2)$$ language sql; 
create operator || (procedure = concat_character, leftarg = character,
rightarg = anyelement);
it works fine with argument of type int,smallint,bigint,date etc.
but its not working with text and varchar data type.
Example:
postgres=# select 'ab'::char(10) || 4::int;
  ?column?   
-
 ab4
(1 row)

postgres=# select 'ab'::char(10) || 'b'::text;
 ?column? 
--
 abb
(1 row)

postgres=# select 'ab'::char(10) || 'b'::varchar(5);
ERROR:  operator is not unique: character || character varying
LINE 1: select 'ab'::char(10) || 'b'::varchar(5);
  ^
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
Thought?



-
Regards,
Vinayak,

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817712.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] copymanager question

[Craig Ringer]

On 09/02/2014 06:20 PM, swaroop wrote:
> To summarize - how do i replace the input strings in java (data is streamed
> in and i do a copy to postgres)
> so that CSV copy does not fail.
> a. words with comma
> b. words with double quotes in them
> c. words with \ (backslash)

Don't write the escaping yourself.

Use a CSV library that can consume your input data and emit correct CSV.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] GiST index question

[Giuseppe Broccolo]

Hi Eric,

As Michael said, path data type does not support for gist operators.
Anyway, you could redefine data type using 'ltree' instead of 'path'. Take
a look on the following link:

http://www.postgresql.org/docs/9.1/static/ltree.html

Try to understand if this could be fine for you.

Cheers,

Giuseppe.


2014-09-04 6:31 GMT+02:00 Michael Paquier :

> On Thu, Sep 4, 2014 at 8:35 AM, Eric Fleming  wrote:
> > I have a table that I have defined as:
> >
> > CREATE TABLE test (
> > "id" SERIAL PRIMARY KEY,
> > "first_path" path NOT NULL,
> > "second_path" path NOT NULL
> > );
> >
> > I am attempting to create a GiST index on the two "path" columns using:
> >
> > CREATE INDEX  "idx_test_first_path" ON test USING gist(first_path);
> > CREATE INDEX  "idx_test_second_path" ON test USING gist(second_path);
> >
> > I am getting this error:
> >
> > Error : ERROR:  data type path has no default operator class for access
> > method "gist"
> >
> > Is it that I am not allowed to create an index on the path data type or
> do I
> > need to enable an extension for this to work? Thanks in advance for your
> > help.
> In-core contrib modules (and core) do not have yet support for gist
> operator classes for the datatype path as far as I recall.
> Regards,
> --
> Michael
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it

[GENERAL] Merge requirements between offline clients and central database

[Sameer Thakur]

Hello,
As part of database evaluation one key requirements is as follows:

1. There are multiple thick clients (say 20 ~ 100)  with their local
databases accepting updates
2. They sync data with a central database which can also receive updates itself.
3. They may not be connected to central database all the time.
4. The central database receives and merges client data, but does not
push data back to clients i.e. data between clients is not synced via
central database or any other way.
Is there anyway PostgreSQL, with any open source tool, can support
such a scenario? How close can it be met?
Any suggestions perhaps on a building a tool outside core which could
bridge any gaps would help too
Thank you
Sameer


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general