[GENERAL] Build libpq on Win7 failed with error "U1045: spawn failed"

[Brilliantov Kirill Vladimirovich]

Hello!
I try build postgresql-9.3.5 on Windows7 x64 with VisualStudioExpess2013.

c:\postgresql-9.3.5\src>nmake -f win32.mak CPU=AMD64

Microsoft (R) Program Maintenance Utility Version 12.00.21005.1
Copyright (C) Microsoft Corporation.  All rights reserved.

cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
NMAKE : fatal error U1045: spawn failed : Invalid argument
Stop.

Use error code I found http://support.microsoft.com/kb/276593 , but 
ComSpec is correct.


c:\postgresql-9.3.5\src>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\user\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=VIRTUAL
ComSpec=C:\Windows\system32\cmd.exe;
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\user
LOCALAPPDATA=C:\Users\user\AppData\Local
LOGONSERVER=\\VIRTUAL
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32
\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL 
Server\110\Tools\Binn\;C

:\Program Files (x86)\Microsoft Visual Studio 12.0\VC\bin
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=AMD64 Family 16 Model 4 Stepping 3, AuthenticAMD
PROCESSOR_LEVEL=16
PROCESSOR_REVISION=0403
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\user\AppData\Local\Temp
TMP=C:\Users\user\AppData\Local\Temp
USERDOMAIN=virtual
USERNAME=user
USERPROFILE=C:\Users\user
windir=C:\Windows
windows_tracing_flags=3
windows_tracing_logfile=C:\BVTBin\Tests\installpackage\csilogfile.log

Can you help me solve this problem?
Thank you and excuse me for my bad english.
--
Best regards,
Brilliantov Kirill Vladimirovich



--
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] Build libpq on Win7 failed with error "U1045: spawn failed"

[Adrian Klaver]

On 10/28/2014 07:10 AM, Brilliantov Kirill Vladimirovich wrote:

Hello!
I try build postgresql-9.3.5 on Windows7 x64 with VisualStudioExpess2013.

c:\postgresql-9.3.5\src>nmake -f win32.mak CPU=AMD64

Microsoft (R) Program Maintenance Utility Version 12.00.21005.1
Copyright (C) Microsoft Corporation.  All rights reserved.

 cd include
 if not exist pg_config.h copy pg_config.h.win32 pg_config.h
NMAKE : fatal error U1045: spawn failed : Invalid argument
Stop.





Can you help me solve this problem?
Thank you and excuse me for my bad english.


First let me say I have not built Postgres on Windows. I did find this:

http://www.postgresql.org/docs/9.3/interactive/install-windows-full.html

One thing that stands out is:

"All commands should be run from the src\tools\msvc directory."

You might want to read through the above documentation, as I suspect you 
are getting a 32/64 mismatch.




--
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] Build libpq on Win7 failed with error "U1045: spawn failed"

[Brilliantov Kirill Vladimirovich]

Adrian Klaver wrote on 10/28/2014 05:24 PM:



First let me say I have not built Postgres on Windows. I did find this:

http://www.postgresql.org/docs/9.3/interactive/install-windows-full.html

One thing that stands out is:

"All commands should be run from the src\tools\msvc directory."

You might want to read through the above documentation, as I suspect you
are getting a 32/64 mismatch.






Hello, Adrian!
But according with 
http://www.postgresql.org/docs/8.3/static/install-win32-libpq.html I 
should run all command in postgres src directory.
For installation I use postgresql-9.3.5-3-windows-x64.exe file, for 
build postgresql-9.3.4.tar.bz2.



--
Best regards,
Brilliantov Kirill Vladimirovich



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

Re: [GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[Andres Freund]

On 2014-10-25 13:55:57 -0700, Joe Van Dyk wrote:
> One of my postgres backends was killed by the oom-killer. Now, one of my
> streaming replication slaves is reporting "invalid contrecord length 2190
> at A6C/331AAA90" in the logs and replication has paused. I have other
> streaming replication slaves that are fine.

Is it a LOG or a PANIC message? Because it's not unexpected to see such
messages when reaching the end of the local and/or restore_command
provided WAL.

> I'm running 9.3.5 on the master. I have 9.3.4 on the slave that has the
> problem, and 9.3.5 on the slave that doesn't have the problem. Is this
> something that was fixed in 9.3.5?

We have really no information to answer that question accurately.

So you really need to provide logs and such.

Greetings,

Andres Freund

-- 
 Andres Freund 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] Build libpq on Win7 failed with error "U1045: spawn failed"

[Adrian Klaver]

On 10/28/2014 07:37 AM, Brilliantov Kirill Vladimirovich wrote:

Adrian Klaver wrote on 10/28/2014 05:24 PM:



First let me say I have not built Postgres on Windows. I did find this:

http://www.postgresql.org/docs/9.3/interactive/install-windows-full.html

One thing that stands out is:

"All commands should be run from the src\tools\msvc directory."

You might want to read through the above documentation, as I suspect you
are getting a 32/64 mismatch.






Hello, Adrian!
But according with
http://www.postgresql.org/docs/8.3/static/install-win32-libpq.html I
should run all command in postgres src directory.
For installation I use postgresql-9.3.5-3-windows-x64.exe file, for
build postgresql-9.3.4.tar.bz2.


My mistake, I read the body of the message and did not pay attention to 
the subject, so I thought you where trying to build a complete version 
of Postgres. Someone else is going to have to take it from here, I just 
do not know enough about the Windows build process.








--
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 find earlest possible start times for given duration excluding reservations

[Andrus]

Hi!

I'm looking for finding ealiest possible start times from reservations 
table.


People work from 10:00AM to 21:00PM in every week day except Sunday and 
public holidays.


Jobs for them are reserved at 15 minute intervals and whole job must fit to 
single day.

Job duration is from 15 minutes to 4 hours.

Reservat table contains reservations, yksus2 table contains workes and
pyha table contains public holidays. Table structures are below. Reservat 
structure can changed if this helps.


How to first earliest 30 possible start times considering existing 
reservations ?


For example, Mary has already reservation at 12:30 .. 16:00 and
John has already reservation at 12:00 to 13:00

In this case query for job with duration of 1.5 hours should return

   John 2014-10-28 10:00
   Mary 2014-10-28 10:00
   John 2014-10-28 10:30
   Mary 2014-10-28 10:30
   Mary 2014-10-28 11:00
   John 2014-10-28 13:00
   Mary 2014-10-28 16:00
   Mary 2014-10-28 16:30
   ... etc and also starting from next days

I tried query based on answer in 
http://stackoverflow.com/questions/13433863/how-to-return-only-work-time-from-reservations-in-postgresql 
below but it returns wrong result:


   MARY  2014-10-28 13:00:00
   MARY  2014-10-29 22:34:40.850255
   JOHN  2014-10-30 22:34:40.850255
   MARY  2014-10-31 22:34:40.850255
   MARY  2014-11-03 22:34:40.850255

Also sliding start times 10:00, 10:30 etc are not returned.

How to get proper first reservations ?

Query which I tried is

   insert into reservat (objekt2, during) values
   ('MARY', '[2014-10-28 11:30:00,2014-10-28 13:00:00)'),
   ('JOHN', '[2014-10-28 10:00:00,2014-10-28 11:30:00)');

   with gaps as (
   select
   yksus,
   upper(during) as start,
   lead(lower(during),1,upper(during)) over (ORDER BY during) - 
upper(during) as gap

   from (
   select
  yksus2.yksus,
  during
 from reservat join yksus2 on reservat.objekt2=yksus2.yksus
 where  upper(during)>= current_date
   union all
   select
   yksus2.yksus,
   unnest(case
   when pyha is not null then array[tsrange1(d, d + 
interval '1 day')]
   when date_part('dow', d) in (0, 6) then 
array[tsrange1(d, d + interval '1 day')]

   when d::date =  current_Date then array[
   tsrange1(d, current_timestamp ),
   tsrange1(d + interval '20 hours', d + 
interval '1 day')]

   else array[tsrange1(d, d + interval '8 hours'),
  tsrange1(d + interval '20 hours', d + 
interval '1 day')]

   end)
   from yksus2, generate_series(
   current_timestamp,
   current_timestamp + interval '1 month',
   interval '1 day'
   ) as s(d)
   left join pyha on pyha = d::date
   ) as x
   )

   select yksus, start
 from gaps
   where gap >= interval'1hour 30 minutes'
   order by start
   limit 30


Schema:

   CREATE EXTENSION btree_gist;
   CREATE TABLE Reservat (
 id serial primary key,
 objekt2 char(10) not null references yksus2 on update cascade 
deferrable,

 during tsrange not null check(
lower(during)::date = upper(during)::date
and lower(during) between current_date and current_date+ 
interval'1 month'


and (lower(during)::time >= '10:00'::time and 
upper(during)::time < '21:00'::time)

AND EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45)
 AND EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30, 45)
and (date_part('dow', lower(during)) in (1,2,3,4,5,6)
and date_part('dow', upper(during)) in (1,2,3,4,5,6))
 ),

 EXCLUDE USING gist (objekt2 WITH =, during WITH &&)
   );

   create or replace function holiday_check() returns trigger language 
plpgsql stable as $$

   begin
   if exists (select * from pyha  where pyha in 
(lower(NEW.during)::date, upper(NEW.during)::date)) then

   raise exception 'public holiday %', lower(NEW.during) ;
   else
   return NEW;
   end if;
   end;
   $$;

   create trigger holiday_check_i before insert or update on Reservat for 
each row execute procedure holiday_check();


   CREATE OR REPLACE FUNCTION public.tsrange1(start timestamp with time 
zone,

   finish timestamp with time zone ) RETURNS tsrange AS
   $BODY$
   SELECT tsrange(start::timestamp without time zone, finish::timestamp 
without time zone );

   $BODY$ language sql immutable;


   -- Workers
   create table yksus2( yksus char(10) primary key);
   insert into yksus2 values ('JOHN'), ('MARY');

   -- public holidays
   create table pyha( pyha date primary key);


I posted it also in 
http://stackoverflow.com/questions/26608683/how-to-find-first-free-start-times-from-reservations-in-postgres


Andrus.

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

[Thom Brown]

On 28 October 2014 15:10, Andrus  wrote:

> Hi!
>
> I'm looking for finding ealiest possible start times from reservations
> table.
>
> People work from 10:00AM to 21:00PM in every week day except Sunday and
> public holidays.
>
> Jobs for them are reserved at 15 minute intervals and whole job must fit
> to single day.
> Job duration is from 15 minutes to 4 hours.
>
> Reservat table contains reservations, yksus2 table contains workes and
> pyha table contains public holidays. Table structures are below. Reservat
> structure can changed if this helps.
>
> How to first earliest 30 possible start times considering existing
> reservations ?
>
> For example, Mary has already reservation at 12:30 .. 16:00 and
> John has already reservation at 12:00 to 13:00
>
> In this case query for job with duration of 1.5 hours should return
>
>John 2014-10-28 10:00
>Mary 2014-10-28 10:00
>John 2014-10-28 10:30
>Mary 2014-10-28 10:30
>Mary 2014-10-28 11:00
>John 2014-10-28 13:00
>Mary 2014-10-28 16:00
>Mary 2014-10-28 16:30
>... etc and also starting from next days
>
> I tried query based on answer in http://stackoverflow.com/
> questions/13433863/how-to-return-only-work-time-from-
> reservations-in-postgresql below but it returns wrong result:
>
>MARY  2014-10-28 13:00:00
>MARY  2014-10-29 22:34:40.850255
>JOHN  2014-10-30 22:34:40.850255
>MARY  2014-10-31 22:34:40.850255
>MARY  2014-11-03 22:34:40.850255
>
> Also sliding start times 10:00, 10:30 etc are not returned.
>
> How to get proper first reservations ?
>
> Query which I tried is
>
>insert into reservat (objekt2, during) values
>('MARY', '[2014-10-28 11:30:00,2014-10-28 13:00:00)'),
>('JOHN', '[2014-10-28 10:00:00,2014-10-28 11:30:00)');
>
>with gaps as (
>select
>yksus,
>upper(during) as start,
>lead(lower(during),1,upper(during)) over (ORDER BY during) -
> upper(during) as gap
>from (
>select
>   yksus2.yksus,
>   during
>  from reservat join yksus2 on reservat.objekt2=yksus2.yksus
>  where  upper(during)>= current_date
>union all
>select
>yksus2.yksus,
>unnest(case
>when pyha is not null then array[tsrange1(d, d +
> interval '1 day')]
>when date_part('dow', d) in (0, 6) then
> array[tsrange1(d, d + interval '1 day')]
>when d::date =  current_Date then array[
>tsrange1(d, current_timestamp ),
>tsrange1(d + interval '20 hours', d +
> interval '1 day')]
>else array[tsrange1(d, d + interval '8 hours'),
>   tsrange1(d + interval '20 hours', d +
> interval '1 day')]
>end)
>from yksus2, generate_series(
>current_timestamp,
>current_timestamp + interval '1 month',
>interval '1 day'
>) as s(d)
>left join pyha on pyha = d::date
>) as x
>)
>
>select yksus, start
>  from gaps
>where gap >= interval'1hour 30 minutes'
>order by start
>limit 30
>
>
> Schema:
>
>CREATE EXTENSION btree_gist;
>CREATE TABLE Reservat (
>  id serial primary key,
>  objekt2 char(10) not null references yksus2 on update cascade
> deferrable,
>  during tsrange not null check(
> lower(during)::date = upper(during)::date
> and lower(during) between current_date and current_date+
> interval'1 month'
>
> and (lower(during)::time >= '10:00'::time and
> upper(during)::time < '21:00'::time)
> AND EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45)
>  AND EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30, 45)
> and (date_part('dow', lower(during)) in (1,2,3,4,5,6)
> and date_part('dow', upper(during)) in (1,2,3,4,5,6))
>  ),
>
>  EXCLUDE USING gist (objekt2 WITH =, during WITH &&)
>);
>
>create or replace function holiday_check() returns trigger language
> plpgsql stable as $$
>begin
>if exists (select * from pyha  where pyha in
> (lower(NEW.during)::date, upper(NEW.during)::date)) then
>raise exception 'public holiday %', lower(NEW.during) ;
>else
>return NEW;
>end if;
>end;
>$$;
>
>create trigger holiday_check_i before insert or update on Reservat for
> each row execute procedure holiday_check();
>
>CREATE OR REPLACE FUNCTION public.tsrange1(start timestamp with time
> zone,
>finish timestamp with time zone ) RETURNS tsrange AS
>$BODY$
>SELECT tsrange(start::timestamp without time zone, finish::timestamp
> without time zone );
>$BODY$ language sql immutable;
>
>
>-- Workers
>create table yksus2( yksus char(10) 

[GENERAL] cannot drop user

[Michael P. Soulier]

Hi,

I've dropped a db and now I'm trying to drop the user that owns all of it, but
I can't. 

dropdb: database removal failed: ERROR:  database "tugdb" does not exist
[2014-10-28 13:23:40,462] INFO:Dropping user...
dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
be dropped because some objects depend on it
DETAIL:  owner of table taps
owner of table siptrunks
owner of table siptrunkroutingrules
owner of sequence sipoptions_id_seq
owner of table sipoptions
owner of table sip_clients
owner of table schema
owner of table proxies
owner of sequence minetoptions_id_seq
owner of table minetoptions
owner of table minet_clients
owner of sequence metrics_id_seq
owner of table metrics
owner of sequence iptranslations_id_seq
owner of table iptranslations
owner of sequence instances_id_seq
owner of table instances
owner of table icps
owner of table dntaps
owner of table django_session
owner of sequence django_content_type_id_seq
owner of table django_content_type
owner of table cres
owner of table config_overrides
owner of table clusterzones
owner of sequence clusters_id_seq
owner of table clusters
owner of table clusternodes
owner of sequence auth_user_user_permissions_id_seq
owner of table auth_user_user_permissions
owner of sequence auth_user_id_seq
owner of sequence auth_user_groups_id_seq
owner of table auth_user_groups
owner of table auth_user
owner of sequence auth_permission_id_seq
owner of table auth_permission
owner of sequence auth_message_id_seq
owner of table auth_message
owner of sequence auth_group_permissions_id_seq
owner of table auth_group_permissions
owner of sequence auth_group_id_seq
owner of table auth_group
owner of table applications
owner of table alarmdevents

The tugdb database is gone but these artifacts are all from it. How is that
possible if the db is gone?

I can't find anything owned by tugdbuser, and I don't understand how to
troubleshoot this. Help appreciated.

This is postgres 8.4 on CentOS 6.

Mike


signature.asc
Description: Digital signature

Re: [GENERAL] cannot drop user

[Jerry Sievers]

"Michael P. Soulier"  writes:

> Hi,
>
> I've dropped a db and now I'm trying to drop the user that owns all of it, but
> I can't. 
>
> dropdb: database removal failed: ERROR:  database "tugdb" does not exist
> [2014-10-28 13:23:40,462] INFO:Dropping user...
> dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
> be dropped because some objects depend on it
> DETAIL:  owner of table taps
> owner of table siptrunks

That user owns objects in whatever DB you're sitting in meanwhile trying
to DROP ROLE.  (template1?)

This is evident below since  you're getting full object names in the
dependency messages.

> owner of table siptrunkroutingrules
> owner of sequence sipoptions_id_seq
> owner of table sipoptions
> owner of table sip_clients
> owner of table schema
> owner of table proxies
> owner of sequence minetoptions_id_seq
> owner of table minetoptions
> owner of table minet_clients
> owner of sequence metrics_id_seq
> owner of table metrics
> owner of sequence iptranslations_id_seq
> owner of table iptranslations
> owner of sequence instances_id_seq
> owner of table instances
> owner of table icps
> owner of table dntaps
> owner of table django_session
> owner of sequence django_content_type_id_seq
> owner of table django_content_type
> owner of table cres
> owner of table config_overrides
> owner of table clusterzones
> owner of sequence clusters_id_seq
> owner of table clusters
> owner of table clusternodes
> owner of sequence auth_user_user_permissions_id_seq
> owner of table auth_user_user_permissions
> owner of sequence auth_user_id_seq
> owner of sequence auth_user_groups_id_seq
> owner of table auth_user_groups
> owner of table auth_user
> owner of sequence auth_permission_id_seq
> owner of table auth_permission
> owner of sequence auth_message_id_seq
> owner of table auth_message
> owner of sequence auth_group_permissions_id_seq
> owner of table auth_group_permissions
> owner of sequence auth_group_id_seq
> owner of table auth_group
> owner of table applications
> owner of table alarmdevents
>
> The tugdb database is gone but these artifacts are all from it. How is that
> possible if the db is gone?
>
> I can't find anything owned by tugdbuser, and I don't understand how to
> troubleshoot this. Help appreciated.
>
> This is postgres 8.4 on CentOS 6.
>
> Mike

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] cannot drop user

[Tom Lane]

"Michael P. Soulier"  writes:
> I've dropped a db and now I'm trying to drop the user that owns all of it, but
> I can't. 

> dropdb: database removal failed: ERROR:  database "tugdb" does not exist
> [2014-10-28 13:23:40,462] INFO:Dropping user...
> dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
> be dropped because some objects depend on it
> DETAIL:  owner of table taps
> owner of table siptrunks
> ... etc

> The tugdb database is gone but these artifacts are all from it. How is that
> possible if the db is gone?

Those DETAIL lines are complaining about objects that are in the database
you're currently attached to; the details about object names and so on
would not be available otherwise.  So I suspect at some point you
accidentally loaded a pg_dump script or suchlike into some other database
besides the tugdb one ...

DROP OWNED BY might be the easiest way to clean up the mess.

regards, tom lane


-- 
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] cannot drop user

[Michael P. Soulier]

On 28/10/14 Jerry Sievers said:

> That user owns objects in whatever DB you're sitting in meanwhile trying
> to DROP ROLE.  (template1?)

I'm just running the dropuser command, so if it uses template1, then yes.

I'm not sure how they would get there. This is only happening on one box, so
perhaps it has odd history.

Thanks,
Mike


signature.asc
Description: Digital signature

Re: [GENERAL] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[Joe Van Dyk]

On Tue, Oct 28, 2014 at 7:43 AM, Andres Freund 
wrote:

> On 2014-10-25 13:55:57 -0700, Joe Van Dyk wrote:
> > One of my postgres backends was killed by the oom-killer. Now, one of my
> > streaming replication slaves is reporting "invalid contrecord length 2190
> > at A6C/331AAA90" in the logs and replication has paused. I have other
> > streaming replication slaves that are fine.
>
> Is it a LOG or a PANIC message? Because it's not unexpected to see such
> messages when reaching the end of the local and/or restore_command
> provided WAL.
>

It's a log message. The server is still running, just replication has
paused.


>
> > I'm running 9.3.5 on the master. I have 9.3.4 on the slave that has the
> > problem, and 9.3.5 on the slave that doesn't have the problem. Is this
> > something that was fixed in 9.3.5?
>
> We have really no information to answer that question accurately.
>
> So you really need to provide logs and such.
>

I'll try to find something next time it happens.

Joe


>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] some queries on standby preventing replication updates

[Joe Van Dyk]

On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo <
emanuel.ca...@2ndquadrant.com> wrote:

>
> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
> > Hi,
> >
> > I have a master and a slave database.
> >
> > I've got hot_standby_feedback turned on,
> > max_standby_streaming_delay=-1. I've configured the master and slave
> > to keep a few days of WALs around.
> >
> > I've noticed that when some large queries are run on the standby
> > machine (ones that take more than a minute or so), replication updates
> > are paused. Is there a way to fix this?
> >
> You may need to set a value on max_standby_streaming_delay, which
> controls the time
> before cancelling the standby queries when a conflict occurs on a
> wal-records-about-to-be-applied.
>
> Source:
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html


I'm using -1 for that option, would using something different be better?

Re: [GENERAL] some queries on standby preventing replication updates

[Emanuel Calvo]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA512



El 28/10/14 a las 15:37, Joe Van Dyk escibió:
> On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo < 
> emanuel.ca...@2ndquadrant.com> wrote:
> 
>> 
>> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
>>> Hi,
>>> 
>>> I have a master and a slave database.
>>> 
>>> I've got hot_standby_feedback turned on, 
>>> max_standby_streaming_delay=-1. I've configured the master and 
>>> slave to keep a few days of WALs around.
>>> 
>>> I've noticed that when some large queries are run on the 
>>> standby machine (ones that take more than a minute or so), 
>>> replication updates are paused. Is there a way to fix this?
>>> 
>> You may need to set a value on max_standby_streaming_delay, which
>> controls the time before cancelling the standby queries when a
>> conflict occurs on a wal-records-about-to-be-applied.
>> 
>> Source: 
>> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
>
>>
>> 
> 
> I'm using -1 for that option, would using something different be 
> better?
> 

Actually that means it will wait that the queries run forever. You
should think which is your maximum threshold regarding query duration
when conflicting with writes.



- -- 
- --
Emanuel Calvo  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Bs. As., Argentina (GMT-3)
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.18 (Darwin)
Comment: GPGTools - http://gpgtools.org

iQIcBAEBCgAGBQJUT+Z7AAoJEIBeI/HMagHmBjwQAKpLJBXzaeRy1zf/fOYR0sce
joaIcyKXIJSwV9Eikc2LPbskfFVnZFoCdSfxL5/JmPNwBgramwinzoosDl/QbLwU
sY1OvTbP7BBMiKGQyLR2zmqHtKBJ+BI92zLSay8qTjOWfZARakMH9CMNK+zqj2OZ
ZTIBa5mtDsrmueWHchjtOx+hNsXfMKo1La2qPz3UIoAns1ti+ZtFDObP6kkYzhhL
4kpSxtHjksG0Rdp7drVs0LC2NgPl71Fy9BznNHAL53LGec1IniS4fkSQSo7XQKNc
XZzq13VDLWFvyaNQ9eFlYujJlEwTKE3c7ygATjLodVAlz4UcY7mBXQOfAxUMKMsh
EgjAfETdy2vE3qgSV0FcPnt/mdgcGjfBg6wqn/JhMuuk7MUovmu96vJ98Ksqm2sH
yYx3bikaHUkE+OL0zfWUGYsIYuqMFTBmnEepvoXW/O6IeAv+b6qzTWtN4lP4K+s1
P5OI/SN/2shuAAvVr1Vhmc3cXYSWKIuu9c8MbTviULwiARl727m6oDU2vqiK8dGa
KgdPsLHkiuxINouu86auE4fbeK68bYIM8fCK19meCLI50L0v1WLbfW+OKIvYnO8K
tT3dE3yejjmWBj00UxQ2PD+3yeLySs6WJrGdYhzY9HtgWocrY3HLDtv5J8+ZB4ww
EbYXrR2Yl5UKGjIlGfu+
=nIRk
-END PGP SIGNATURE-


-- 
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] some queries on standby preventing replication updates

[David G Johnston]

Joe Van Dyk wrote
> On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo <

> emanuel.calvo@

>> wrote:
> 
>>
>> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
>> > Hi,
>> >
>> > I have a master and a slave database.
>> >
>> > I've got hot_standby_feedback turned on,
>> > max_standby_streaming_delay=-1. I've configured the master and slave
>> > to keep a few days of WALs around.
>> >
>> > I've noticed that when some large queries are run on the standby
>> > machine (ones that take more than a minute or so), replication updates
>> > are paused. Is there a way to fix this?
>> >
>> You may need to set a value on max_standby_streaming_delay, which
>> controls the time
>> before cancelling the standby queries when a conflict occurs on a
>> wal-records-about-to-be-applied.
>>
>> Source:
>> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
> 
> I'm using -1 for that option, would using something different be better?

Why did you choose -1?

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/some-queries-on-standby-preventing-replication-updates-tp5824090p5824675.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] some queries on standby preventing replication updates

[Torsten Förtsch]

On 28/10/14 19:37, Joe Van Dyk wrote:
> On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo
> mailto:emanuel.ca...@2ndquadrant.com>>
> wrote:
> 
> 
> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
> > Hi,
> >
> > I have a master and a slave database.
> >
> > I've got hot_standby_feedback turned on,
> > max_standby_streaming_delay=-1. I've configured the master and slave
> > to keep a few days of WALs around.
> >
> > I've noticed that when some large queries are run on the standby
> > machine (ones that take more than a minute or so), replication updates
> > are paused. Is there a way to fix this?
> >
> You may need to set a value on max_standby_streaming_delay, which
> controls the time
> before cancelling the standby queries when a conflict occurs on a
> wal-records-about-to-be-applied.
> 
> Source:
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
> 
> 
> I'm using -1 for that option, would using something different be better? 

It depends on what you want to achieve. If you want to sacrifice your
long-running query to keep replication going, set the value to >0. If
you (like me) are using the slave to run analytical queries that can
take many hours or even days, I'd rather live with the current
behaviour. When the long-running query is over the wal receiver
automatically reconnects to the master. The only thing you should make
sure is to keep enough wal segments. With 9.4 even that gets easier.
There you can assign a replication slot to the replica and the master
then knows which segments are still needed when the slave reconnects.

Torsten


-- 
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 find earlest possible start times for given duration excluding reservations

[Andrus]

Hi!

>Would you be able to adapt this to your needs?:
Thank you very much. Great solution.
I refactored it as shown below.
Query returns only dates for single day. Changing limit clause to 300 does not 
return next day.
How to return other day dates also, excluding sundays and public holidays in 
pyha table ?
Andrus.
Testcase is:
create table pyha (pyha date primary key);
insert into pyha(pyha) values('2014-10-29');
create table  yksus2(yksus char(10) primary key);
insert into yksus2 values ('JOHN'),('MARY');
CREATE EXTENSION btree_gist;

CREATE TABLE reservat
(
  reservat_id serial primary key,
  objekt2 char(10) not null references yksus2 on update cascade deferrable,
during tstzrange not null,
EXCLUDE USING gist (objekt2 WITH =, during WITH &&),

CONSTRAINT same_date
 CHECK (lower(during)::date = upper(during)::date),

CONSTRAINT max_1month_future 
 CHECK (lower(during) between current_date and current_date+ interval'1 
month' ),

CONSTRAINT time_between_1000_and_2100
 CHECK (lower(during)::time >= '10:00'::time and upper(during)::time < 
'21:00'::time),

CONSTRAINT lower_bound_included
 CHECK (lower_inc(during)),

CONSTRAINT upper_bound_excluded
 CHECK (not upper_inc(during)),

CONSTRAINT start_time_at_15minute_offset
 CHECK (EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45)),
-- or (extract(epoch from lower(during)::time)::int % (60*15) = 0)

CONSTRAINT end_time_at_15minute_offset
 CHECK (EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30,45)),

CONSTRAINT duration_between_15min_and_4hours
 CHECK (upper(during) - lower(during) between '15 mins'::interval and '4 
hours'::interval),

CONSTRAINT exclude_sundays
 CHECK (date_part('dow', lower(during)) in (1,2,3,4,5,6) )
);

create or replace function holiday_check() returns trigger language plpgsql 
stable as $$
begin
if exists (select * from pyha  where pyha between 
lower(NEW.during)::date and upper(NEW.during)::date) then
raise exception 'public holiday %', lower(NEW.during) ;
else
return NEW;
end if;
end;
$$;

create trigger holiday_check_i before insert or update on Reservat for each row 
execute procedure holiday_check();
INSERT INTO reservat (objekt2, during)
  VALUES ('MARY','[2014-10-28 11:30+2,2014-10-28 13:00+2)'::tstzrange);
INSERT INTO reservat (objekt2, during)
  VALUES ('JOHN','[2014-10-28 10:00+2,2014-10-28 11:30+2)'::tstzrange);

SELECT yksus2.yksus, times.period
FROM generate_series('2014-10-28 10:00+2'::timestamptz, '2014-10-28 21:00+2', 
'15 mins'::interval) times(period)
CROSS JOIN yksus2
LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30 
mins'::interval, '[)') && reservat.during AND yksus2.yksus = reservat.objekt2
WHERE reservat.during IS NULL
ORDER BY 2, 1
LIMIT 300;

[GENERAL] "can not able to find scan Function For making NoDB"

[Gaurav Kumar]

Sir,

I am working on NoDB But In debug mode of eclipse, i am not able to 
find scan function that is used for loading a table.


Please help me i am not able to go ahead.

Regards,

gaurav kumar



--
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] Log-shipping replication in one machine

[nurul [via PostgreSQL]]

Thank you for your response. May i know what is the difference between log
shipping and streaming replication actually? I'm sorry i am very new in
postgreSQL and still confused with these two  




__
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Log-shipping-replication-in-one-machine-tp5823774p5824365.html
This email was sent by nurul (via Nabble)

Re: [GENERAL] "can not able to find scan Function For making NoDB"

[Adrian Klaver]

On 10/24/2014 11:13 AM, Gaurav Kumar wrote:

Sir,

I am working on NoDB But In debug mode of eclipse, i am not able to find
scan function that is used for loading a table.

Please help me i am not able to go ahead.


This is the Postgres list. I think you need to find the NoDB list.



Regards,

gaurav kumar






--
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] pg killed by oom-killer, "invalid contrecord length 2190 at A6C/331AAA90" on slaves

[basti]

Hello,

months ago I have a similar problem with the OOM-Killer.
Have a look at
http://www.credativ.co.uk/credativ-blog/2010/03/postgresql-and-linux-memory-management

I hope that's helpful.

Regards,
basti

On Sat 25.10.2014 22:55 +0200, Joe Van Dyk  wrote:
> One of my postgres backends was killed by the oom-killer. Now, one of my
> streaming replication slaves is reporting "invalid contrecord length
> 2190 at A6C/331AAA90" in the logs and replication has paused. I have
> other streaming replication slaves that are fine.
> 
> Is that expected? It's happened twice in two days.
> 
> I'm running 9.3.5 on the master. I have 9.3.4 on the slave that has the
> problem, and 9.3.5 on the slave that doesn't have the problem. Is this
> something that was fixed in 9.3.5?
> 
> The slave that has the problem is also located across the country, while
> the slave that works is in the same data center as the master -- not
> sure if that's related at all.
> 
> Joe


-- 
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] Log-shipping replication in one machine

[Adrian Klaver]

On 10/26/2014 09:46 PM, nurul [via PostgreSQL] wrote:

Thank you for your response. May i know what is the difference between
log shipping and streaming replication actually? I'm sorry i am very new
in postgreSQL and still confused with these two



For an overview see:

http://www.postgresql.org/docs/9.3/interactive/warm-standby.html

Short version:

1) Both deal with WAL files.

2) Log shipping ships the entire file (16MB by default) at a time.

3) Streaming ships records within the WAL file, so it works incrementally.


--
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] Need guidance on regression.diffs

[Adrian Klaver]

On 10/28/2014 02:21 AM, Romu Hu wrote:

Hi,

I'm new to postgresql.  I ran the regression tests
 on my
postgresql 9.2.8, and 17 of 131 tests failed. Below is the
regression.diffs (sorry for pasting such a long file), I have no clue
whether these failures can be safely ignored.  Any guidance would be
appreciated.



Did you run this  against the temporary installation as in section

30.1.1. Running the Tests Against a Temporary Installation

or

30.1.2. Running the Tests Against an Existing Installation?

In particular if running against an existing installation, is it 
possible you ran against a different version of Postgres then 9.2.8?


This could happen if you have the env variables  PGHOST and PGPORT set 
to another Postgres instance.





Thanks
Romu



--
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] How to find earlest possible start times for given duration excluding reservations

[Thom Brown]

On 28 October 2014 19:14, Andrus  wrote:

>   Hi!
>
>  >Would you be able to adapt this to your needs?:
>
>  Thank you very much.
> Great solution.
>  I refactored it as shown below.
>  Query returns only dates for single day. Changing limit clause to 300
> does not return next day.
>  How to return other day dates also, excluding sundays and public
> holidays in pyha table ?
>

It's not a robust solution if you need it to span days, but you could just
increment the 2nd timestamptz parameter in the generate_series function
call by a year:

generate_series('2014-10-28 10:00+2'::timestamptz, '2015-10-28 21:00+2',
'15 mins'::interval)

It's hacky, but it should work, but if you happened to have a policy
whereby reservations couldn't be made beyond, say, 3 months in advance, you
could just give it a date 3 months in the future, and make sure that the
first parameter is capped to the same range.

So here's an example of what you could do (although it could probably be
simplified and made more elegant).  Here it will find times from the
current time until 3 months in the future.  It also filters out holiday
dates.

SELECT yksus2.yksus, times.period
FROM generate_series(now()::date::timestamptz, now()::date::timestamptz +
'3 months'::interval, '15 mins'::interval) times(period)
CROSS JOIN yksus2
LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30
mins'::interval, '[)') && reservat.during
  AND yksus2.yksus = reservat.objekt2
LEFT JOIN pyha ON times.period::date = pyha.pyha::date
WHERE reservat.during IS NULL
  AND pyha.pyha IS NULL
  AND times.period::time BETWEEN '10:00'::time AND '21:00'::time
  AND times.period >= now()
ORDER BY 2, 1
LIMIT 300;

-- 
Thom

Re: [GENERAL] cannot drop user

[Michael P. Soulier]

On Oct 28, 2014, at 1:42 PM, Tom Lane  wrote:
> Those DETAIL lines are complaining about objects that are in the database
> you're currently attached to; the details about object names and so on
> would not be available otherwise.  So I suspect at some point you
> accidentally loaded a pg_dump script or suchlike into some other database
> besides the tugdb one ...
> 
> DROP OWNED BY might be the easiest way to clean up the mess.

Found it, thanks.

Mike


-- 
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 find earlest possible start times for given duration excluding reservations

[Thom Brown]

On 28 October 2014 20:04, Thom Brown  wrote:

> On 28 October 2014 19:14, Andrus  wrote:
>
>>   Hi!
>>
>>  >Would you be able to adapt this to your needs?:
>>
>>  Thank you very much.
>> Great solution.
>>  I refactored it as shown below.
>>  Query returns only dates for single day. Changing limit clause to 300
>> does not return next day.
>>  How to return other day dates also, excluding sundays and public
>> holidays in pyha table ?
>>
>
> It's not a robust solution if you need it to span days, but you could just
> increment the 2nd timestamptz parameter in the generate_series function
> call by a year:
>
> generate_series('2014-10-28 10:00+2'::timestamptz, '2015-10-28 21:00+2',
> '15 mins'::interval)
>
> It's hacky, but it should work, but if you happened to have a policy
> whereby reservations couldn't be made beyond, say, 3 months in advance, you
> could just give it a date 3 months in the future, and make sure that the
> first parameter is capped to the same range.
>
> So here's an example of what you could do (although it could probably be
> simplified and made more elegant).  Here it will find times from the
> current time until 3 months in the future.  It also filters out holiday
> dates.
>
> SELECT yksus2.yksus, times.period
> FROM generate_series(now()::date::timestamptz, now()::date::timestamptz +
> '3 months'::interval, '15 mins'::interval) times(period)
> CROSS JOIN yksus2
> LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30
> mins'::interval, '[)') && reservat.during
>   AND yksus2.yksus = reservat.objekt2
> LEFT JOIN pyha ON times.period::date = pyha.pyha::date
> WHERE reservat.during IS NULL
>   AND pyha.pyha IS NULL
>   AND times.period::time BETWEEN '10:00'::time AND '21:00'::time
>   AND times.period >= now()
> ORDER BY 2, 1
> LIMIT 300;
>

A correction to this.  As it stands, it will show times like the following:

   yksus| period
+
...
 JOHN   | 2014-10-30 19:45:00+00
 MARY   | 2014-10-30 19:45:00+00
 JOHN   | 2014-10-30 20:00:00+00
 MARY   | 2014-10-30 20:00:00+00
 JOHN   | 2014-10-30 20:15:00+00
 MARY   | 2014-10-30 20:15:00+00
 JOHN   | 2014-10-30 20:30:00+00
 MARY   | 2014-10-30 20:30:00+00
 JOHN   | 2014-10-30 20:45:00+00
 MARY   | 2014-10-30 20:45:00+00
 JOHN   | 2014-10-30 21:00:00+00
 MARY   | 2014-10-30 21:00:00+00
 JOHN   | 2014-10-31 10:00:00+00
 MARY   | 2014-10-31 10:00:00+00
...

This is incorrect a 1.5 hour appointment after 19:30 would go beyond the
working hours.  So that needs to be factored into it:

SELECT yksus2.yksus, times.period
FROM generate_series(now()::date::timestamptz, now()::date::timestamptz +
'3 months'::interval, '15 mins'::interval) times(period)
CROSS JOIN yksus2
LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30
mins'::interval, '[)') && reservat.during
  AND yksus2.yksus = reservat.objekt2
LEFT JOIN pyha ON times.period::date = pyha.pyha::date
WHERE reservat.during IS NULL
  AND pyha.pyha IS NULL
  AND times.period::timetz BETWEEN '10:00'::timetz AND '21:00'::timetz - '1
hour 30 mins'::interval
  AND times.period >= now()
ORDER BY 2, 1
LIMIT 300;

This gives you:

   yksus| period
+
...
 JOHN   | 2014-10-30 19:15:00+00
 MARY   | 2014-10-30 19:15:00+00
 JOHN   | 2014-10-30 19:30:00+00
 MARY   | 2014-10-30 19:30:00+00
 JOHN   | 2014-10-31 10:00:00+00
 MARY   | 2014-10-31 10:00:00+00
 JOHN   | 2014-10-31 10:15:00+00
 MARY   | 2014-10-31 10:15:00+00
...

Regards

Thom

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

[Andrus]

Hi!
>A correction to this.  As it stands, it will show times like the following:
Thank you.
I posted your solution as alternative to Erwin answer in 
http://stackoverflow.com/questions/26608683/how-to-find-first-free-start-times-from-reservations-in-postgres
Andrus.
 

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

[Thom Brown]

On 28 October 2014 21:07, Andrus  wrote:

>   Hi!
>
>  >A correction to this.  As it stands, it will show times like the
> following:
>
>  Thank you.
>  I posted your solution as alternative to Erwin answer in
>
> http://stackoverflow.com/questions/26608683/how-to-find-first-free-start-times-from-reservations-in-postgres
>
>

A further tweak; add the following to the WHERE clause:

AND EXTRACT(DOW FROM times.period) != 0

This will ensure Sundays are excluded.  I don't know if you want Saturdays
excluded, but you can easily adjust it for that.

Thom

[GENERAL] Query optimization

[Jorge Arevalo]

Hello,

I'm trying to optimize a query that takes too much time. This is what I have

table1(field1, field2... field14): contains about 8.5 million rows
table2(f1, f2, f3): contains about 2.5 million rows
table3: is empty, and must be filled with data coming from table1 and table2

To fill table3, I'm using a query that looks like this:

WITH
records_to_insert(field1,field2,field3,field4,field5,field6_hstore,field7,field8_array)
AS

(SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
field10, field11, field12, field13, field14) as metadata, value7, (select
array((select row(f1, f2) from table2 p where p.field7 = field7))) as
values_array FROM table1)

SELECT
fill_table3_function(field1,field2,field3,field4,field5,field6_hstore,field7,field8_array)
FROM records_to_insert

So, I first generate a common table records_to_insert, using data from
table1 and table2, and then call a function fill_table3_function, in order
to insert the values into table3 (I do more things apart from insert,
that's reason to call a function instead of just raising an insert query).
There are indexes created on all the columns that need them.

I'm having problems because the query takes a lot of time, and the server
returns a timeout error.

I think the bottleneck is the array built for the last argument of my
common table, and maybe the hstore too (not sure if it's a heavy process or
not). First thing I've tried is to limit  the query to build the common
table, using LIMIT .. OFFSET after 'FROM table1', and make a loop to seek
table1 (the big one, with 8 million rows). But still getting timeout errors.

The problem is I don't have access to Postgres configuration, in order to
increase the timeout for user queries. And anyway, I don't think that
increasing the timeout is a real solution (It'll just make the server
suffer for more time).

So, is there anything obviously wrong with my query? Any changes to make it
faster?

Many thanks in advance, and best regards,


-- 
Jorge Arevalo
Freelance developer

http://about.me/jorgeas80

Re: [GENERAL] Need guidance on regression.diffs

[Romu Hu]

On 2014/10/29 3:58, Adrian Klaver wrote:

On 10/28/2014 02:21 AM, Romu Hu wrote:

Hi,

I'm new to postgresql.  I ran the regression tests
 on my
postgresql 9.2.8, and 17 of 131 tests failed. Below is the
regression.diffs (sorry for pasting such a long file), I have no clue
whether these failures can be safely ignored.  Any guidance would be
appreciated.



Did you run this  against the temporary installation as in section

30.1.1. Running the Tests Against a Temporary Installation

or

30.1.2. Running the Tests Against an Existing Installation?

In particular if running against an existing installation, is it 
possible you ran against a different version of Postgres then 9.2.8?


This could happen if you have the env variables  PGHOST and PGPORT set 
to another Postgres instance.


I ran the test against an existing installation (redhat enterprise linux 
software collection postgresql92).  The postgres server and the tests 
are from the same source package.


Thanks
Romu


--
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] Query optimization

[David G Johnston]

Jorge Arévalo-2 wrote
> So, I first generate a common table records_to_insert, using data from
> table1 and table2, and then call a function fill_table3_function, in order
> to insert the values into table3 (I do more things apart from insert,
> that's reason to call a function instead of just raising an insert query).
> There are indexes created on all the columns that need them.

I would suggest you figure out how to do what you need without pushing the
insert into the function.


> So, is there anything obviously wrong with my query? Any changes to make
> it
> faster?

You shouldn't expect much useful help when the important part of your query
is not provided.

Creating arrays and hstores is expensive but whether that is the biggest
factor is impossible to tell.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Query-optimization-tp5824739p5824744.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] Query optimization

[David G Johnston]

Jorge Arévalo-2 wrote
> (SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14) as metadata, value7, (select
> array((select row(f1, f2) from table2 p where p.field7 = field7))) as
> values_array FROM table1)

You might try seeing whether:

FROM table1 JOIN (
SELECT field7, array_agg(row(f1, f2)) AS values_array FROM table2 GROUP BY
field7
) tbl2_agg USING (field7)

helps...

I'm also dubious (though this isn't necessarily a performance issue) of:

array[...] AS metadata

Without context I would say this would be better as a composite type instead
of an array.  You may find it useful to use named composite types elsewhere
too...

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Query-optimization-tp5824739p5824746.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: [BUGS] [GENERAL] Need guidance on regression.diffs

[Michael Paquier]

On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu  wrote:
> I ran the test against an existing installation (redhat enterprise linux
> software collection postgresql92).  The postgres server and the tests are
> from the same source package.
Well, your diffs are telling us the contrary. The additional columns
of \d+ have been added by this commit which is a new feature of
Postgres 9.4, introduced by this commit:

commit: 4168c00a5d9c0c0c17cdfc902587b6d22ea1720f
author: Bruce Momjian 
date: Tue, 15 Apr 2014 13:28:54 -0400
psql: conditionally display oids and replication identity

In psql \d+, display oids only when they exist, and display replication
identity only when it is non-default.  Also document the defaults for
replication identity for system and non-system tables.  Update
regression output.

So you may be indeed running the tests on a 9.2 server, but what is
sure is that you are comparing the results with the regression output
of a 9.4 server.
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

Re: [BUGS] [GENERAL] Need guidance on regression.diffs

[Tom Lane]

Michael Paquier  writes:
> On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu  wrote:
>> I ran the test against an existing installation (redhat enterprise linux
>> software collection postgresql92).  The postgres server and the tests are
>> from the same source package.

> Well, your diffs are telling us the contrary.

Yeah, this is definitely some kind of version skew problem.

> So you may be indeed running the tests on a 9.2 server, but what is
> sure is that you are comparing the results with the regression output
> of a 9.4 server.

No, that's not quite right I think.  I can't find anything in a quick
look that is clearly different server behavior.  There are a bunch of
differences in output of \d commands, and a bunch of different formatting
of query results containing newlines, but both of those things are on
psql's head not the server's.  The psql being used is clearly older than
9.0, which is where the display of newlines changed.  I'd bet on it being
the 8.4.something version shipped by Red Hat with their regular RHEL6
postgresql package.

I managed to leave Red Hat before they shipped any of those "software
collections" packages, but when I was there I was pretty unimpressed
with that packaging technology.  You have to use the packages just so
or things fall apart, because for example their programs aren't in the
system default PATH.  I think something like that happened here:
somehow or other the test process is invoking /usr/bin/psql and not
the psql included in the software-collections PG package.

Take a close re-read of the documentation for the software-collections
PG package and see if you missed a setup setup (scenable or whatever it
was called), or maybe they have a special recipe for running the
regression tests.  If you can't find anything, file a bug with Red Hat
(not us), to the effect that their regression test packaging is either
broken or underdocumented.

regards, tom lane


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

Re: [BUGS] [GENERAL] Need guidance on regression.diffs

[Romu Hu]

On 2014/10/29 11:23, Tom Lane wrote:

Michael Paquier  writes:

On Wed, Oct 29, 2014 at 10:44 AM, Romu Hu  wrote:

I ran the test against an existing installation (redhat enterprise linux
software collection postgresql92).  The postgres server and the tests are
from the same source package.

Well, your diffs are telling us the contrary.

Yeah, this is definitely some kind of version skew problem.


So you may be indeed running the tests on a 9.2 server, but what is
sure is that you are comparing the results with the regression output
of a 9.4 server.

No, that's not quite right I think.  I can't find anything in a quick
look that is clearly different server behavior.  There are a bunch of
differences in output of \d commands, and a bunch of different formatting
of query results containing newlines, but both of those things are on
psql's head not the server's.  The psql being used is clearly older than
9.0, which is where the display of newlines changed.  I'd bet on it being
the 8.4.something version shipped by Red Hat with their regular RHEL6
postgresql package.


You are right, the test was using /usr/bin/psql from RHEL6 
postgresql-8.4.20 package, hence the diffs.  I removed postgresql-8.4.20 
and reran the regression tests from the postgresql92 software 
collection, it failed the run because it couldn't find /usr/bin/psql, I 
linked /opt/rh/postgresql92/root/usr/bin/psql to /usr/bin/psql and all 
regression passed.


Thanks
Romu


I managed to leave Red Hat before they shipped any of those "software
collections" packages, but when I was there I was pretty unimpressed
with that packaging technology.  You have to use the packages just so
or things fall apart, because for example their programs aren't in the
system default PATH.  I think something like that happened here:
somehow or other the test process is invoking /usr/bin/psql and not
the psql included in the software-collections PG package.

Take a close re-read of the documentation for the software-collections
PG package and see if you missed a setup setup (scenable or whatever it
was called), or maybe they have a special recipe for running the
regression tests.  If you can't find anything, file a bug with Red Hat
(not us), to the effect that their regression test packaging is either
broken or underdocumented.

regards, tom lane




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

[GENERAL] Appending new data to existing field of Json data type

[VENKTESH GUTTEDAR]

Hello,

As i am new to postgresql, i am learning through experimenting things.

i have a table with json data type field, so there is some data for
example :

{ [ { a:b, b:c } ] }

and now if i append data then it should be like :

{ [ { a:b, b:c }, { e:f, g:h } ] }

Is there any way to achieve this. please help.!
I have Postgresql 9.3.5.
-- 
Regards :
Venktesh Guttedar.