Re: [GENERAL] Very slow update / hash join

[Jeff Janes]

On Wed, May 4, 2016 at 3:22 PM, Kurt Roeckx  wrote:
> Hi,
>
> I have an update query that's been running for 48 hours now.
> Since it started it used about 2.5% CPU, and is writing to the
> disk at about 3 MB/s, and reading at about 2 MB/s.  It's mostly
> waiting for the disks.

The easiest way to figure out what is going on is to identify the
process, and then trace it with something like:

strace -T -ttt -y -p 

That should make it obvious which file it is waiting for IO on.  Then
you can look up that relfilenode in pg_class to see what table/index
it is.


> The table itself is 53GB, and the table it updates from is only
> 3.3 GB.  There are some index on some of the fields (like the id),
> but none of them are being updated.  I tried removing those that
> did get updated but that had little effect.  It does have foreign
> keys to other tables, and other tables references it, but none of
> the keys should get updated.

What version of PostgreSQL are you using?  Have you tried dropping the
foreign keys?

Cheers,

Jeff


-- 
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] Debian and Postgres

[rob stone]

Hello Adrian,On Thu, 2016-05-05 at 13:47 -0700, Adrian Klaver wrote:
> 
> Exactly. Showing the list the error you get when you cannot connect
> help 
> may with solving that problem and save you a great of time. What
> have 
> you got to lose?
> 

I have nothing to "lose".
There is NO error, per se. The progress bars just keep churning and
absolutely nothing happens. All you can do is cancel.
Nothing in the log files. No exceptions thrown. A black hole. I've
waited minutes to see if it can connect, but no. I tried running on the
9.4 cluster but the same thing. 

I appreciate all your suggestions.
Tomorrow I'll talk to a friend of mine who is a Java guru to see if it
is possible to force some kind of stack trace or something that will
provide a clue as to what is happening. If you can't find the driver
you'd expect a DriverManager exception to be thrown or maybe a
ClassNotFound. Not even this. It's the lack of any error message
anywhere that is frustating.

Cheers,
Rob


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

[GENERAL] Trying to create array of enum to array of text for exclusion constraint

[Steven Lembark]

Using Pg 9.5.2 on linux.

Trying to create an exclusion constraint on an array of enums.
Ultimate goal is having a constraint that excludes records with
overlapping elements.

This must have been done before, I just cannot find any examples.

I realize there isn't a q&d way to convert enums to integers
(e.g., 
)
but there should be a way to convert enums to text for this purpose.

For example, with a scalar enum this works:

e.g., 

drop type if exists week_day cascade;
create type week_day as
enum
(
'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'
);

/*
 * works for exclude using gist as "week_day_text( X ) with =".
 */
create or replace function week_day_text
(
  week_day
)
returns text
language sql strict immutable as
$$
  select $1::text;
$$
;

/*
 * this works if days is week_day w/o array and
 * the exclusion uses week_day_text( day ).
 */

drop table if exists timeslot cascade;
create table timeslot
(
/*
 * this would normally also have hours,
 * for this example weekday is sufficient.
 */

day week_day   not null,

exclude using gist
(
week_day_text( day ) with =
)
);


Goal is replacing day with an array of week_day as:

day week_day[] not null,

Using "day with &&" leaves me with (whitespace added):

drop table if exists timeslot cascade;
create table timeslot
(
/*
 * this would normally also have hours,
 * for this example weekday is sufficient.
 */

day week_day[]  not null,   /* add array of enum */

exclude using gist
(
day with &&
)
);

psql:hak:43: ERROR:  data type week_day[] has no 
default operator class for access method "gist"
HINT:  You must specify an operator class for 
the index or define a default operator class for 
the data type.

Using the text function blows up because it doesn't support arrays
(again, whitespace added for readability):

(
...

exclude using gist
(
week_day_text( day ) with &&
)
);


psql:hak:43: ERROR:  function week_day_text(week_day[]) does not exist
LINE 10: week_day_text( day ) with &&
 ^
HINT:  No function matches the given name and 
argument types. You might need to add explicit type casts.


Using array_to_string won't be sufficient since that would allow
overlaps due to different orders of array elements.

So... what I think I need is a plsql function that takes an 
array of weekday and retuns an array of text?

/*
 * convert array of week_day enum values to array of 
 * text for exclusion constraints.
 */

create or replace function week_day_array_text
(
  week_day[]
)
returns text[]
language sql strict immutable as
$$
/*
 * what is the syntax for generating this array?
 * effectively I need a "map { $1::text }" in plsql.
 */
$$
;

or is there something built in that I have missed?

Note: Performance will not be an issue here as the table is not
updated all that frequently.

Any references appreciated.

-- 
Steven Lembark   3646 Flora Pl
Workhorse Computing St Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508


-- 
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 manually force a transaction wraparound

[Eric Ridge]

On Fri, Apr 29, 2016 at 10:16 PM Thomas Munro 
wrote:

> On Sat, Apr 30, 2016 at 10:48 AM, Eric Ridge  wrote:
> > I want to force my database to wraparound, just to see what happens.  How
> > can I do this without consuming a few billion transactions?
>
> Take a look at the script repro-bogus-subtrans-error-wraparound.sh
> from this email:
>
>
> http://www.postgresql.org/message-id/CAEepm=3z0eolpo5wtuwsem38kbq+gjp8xxiuljkuqpm-sw7...@mail.gmail.com
>
> That used pg_resetxlog -x $XID $PGDATA, but needed to do several hops
> stop/pg_resetxlog/start hops to get all the way around the xid clock.


Thanks Thomas.  I ended up figuring out something similar after I read the
docs on pg_resetxlog.

It did something interesting to two of my local databases, but I was able
to figure out what I wanted to know.

Thanks again!

eric

Re: [GENERAL] CREATE OR REPLACE AGGREGATE -- NOT!

[dandl]

Thanks. Much as I expected.

 

No, I don’t think it would be hard to do. Maybe if I ever feel a pressing need 
to learn how to submit a patch it might be something to start with.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G. Johnston
Sent: Thursday, 5 May 2016 2:06 PM
To: dandl 
Cc: Postgres General 
Subject: Re: [GENERAL] CREATE OR REPLACE AGGREGATE -- NOT!

 

On Wednesday, May 4, 2016, dandl mailto:da...@andl.org> > 
wrote:

I was surprised to discover that

* CREATE OR REPLACE AGGREGATE is not allowed, but

* CREATE OR REPLACE FUNCTION works just fine

 

Is this an oversight, or is there some reason I missed? 

 

 Its not an oversight: but whether it is easily accomplished, or worthy of the 
effort, is a different matter.

 

Yes, I know I can do:

DROP AGGREGATE IF EXISTS

CREATE AGGREGATE

 

But this triggers a NOTICE. Is there any convenient way to replace a (possibly) 
existing aggregate function without triggering a NOTICE?

 

Set client_min_messages = warning;

 

David J.

Re: [GENERAL] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

It's working now...

Final code:

ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
> ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT
> NULL;
> ALTER TABLE public.companies ALTER COLUMN client_code_increment SET
> DEFAULT 1000;
> COMMIT TRANSACTION;
>
> BEGIN;
> -- Creating the function
> CREATE OR REPLACE FUNCTION users_code_seq()
>RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> -- if it's an insert, then we update the client_code_increment column
> value to +1
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.companies SET client_code_increment =
> (client_code_increment + 1) WHERE id = NEW.company_id;
> END IF;
> -- IF the customer didn't provide a code value, we insert the next
> available from companies.client_code_increment
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO NEW.code FROM public.companies
> as c WHERE c.id = NEW.company_id ORDER BY client_code_increment DESC;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
> -- Creating the trigger
> CREATE TRIGGER tf_users_code_seq
>BEFORE INSERT
>ON public.users
>FOR EACH ROW
>EXECUTE PROCEDURE users_code_seq();
>
> COMMIT TRANSACTION;


Thanks.
Lucas

Re: [GENERAL] Function PostgreSQL 9.2

[Berend Tober]

David G. Johnston wrote:


​Berend already identified the problem for you.



Thank you.



--
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] Function PostgreSQL 9.2

[Melvin Davidson]

On Thu, May 5, 2016 at 6:17 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
> If I change that to company_id, I get the error:  column "company_id" does
>> not exist, because that column is inside USERS and not COMPANIES.
>>
>>
> ​change that  what is "that"
>
> ​Provide the actual code you ran that resulted in "column "company_id"
> does not exist"​  Your attempts at brevity are making this harder that it
> has to be.
>
> David J.
>
>
> ​
>
>


Drum,
I strongly suggest you learn how to debug your own code.
The best way to do that is to temporarily use RAISE NOTICE ... at various
points in your TRIGGER function so that YOU can determine the exact source
of the problem.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Function PostgreSQL 9.2

[David G. Johnston]

> If I change that to company_id, I get the error:  column "company_id" does
> not exist, because that column is inside USERS and not COMPANIES.
>
>
​change that  what is "that"

​Provide the actual code you ran that resulted in "column "company_id" does
not exist"​  Your attempts at brevity are making this harder that it has to
be.

David J.


​

Re: [GENERAL] Function PostgreSQL 9.2

[David G. Johnston]

>
>
>
> 2) You have a where clause:  company_id = NEW.id
>> 3) NEW refers to users
>> 4) NEW.id is obstensibly a USER ID
>>
>
>
> No...
>
>
​Which one of the three do you disagree with?
​

Re: [GENERAL] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

>
>
> 1) You attached users_code_seq() to a trigger on the users table.
>

yes


> 2) You have a where clause:  company_id = NEW.id
> 3) NEW refers to users
> 4) NEW.id is obstensibly a USER ID
>


No...

CREATE OR REPLACE FUNCTION users_code_seq()
>RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.companies SET client_code_increment =
> (client_code_increment + 1) WHERE id = NEW.id;
> END IF;
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO NEW.code FROM public.companies
> as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;


I'm updating the companies table... The company table has a column called
ID.
If I change that to company_id, I get the error:  column "company_id" does
not exist, because that column is inside USERS and not COMPANIES.

So as far as I can see here, that command is right.

- If I'm wrong, please, explain to me.

5) So you are basically saying: WHERE company_id = user_id
> 6) If you were to get match it would be entirely by accident - say because
> you used the same integer for both id values
>

Just to be clear here:

1 -

> ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
> ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT
> NULL;
> ALTER TABLE public.companies ALTER COLUMN client_code_increment SET
> DEFAULT 1000;



2 -

> CREATE OR REPLACE FUNCTION users_code_seq()
>RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.companies SET client_code_increment =
> (client_code_increment + 1) WHERE company_id = NEW.id;
> END IF;
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO NEW.code FROM public.companies
> as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;


3 -

> CREATE TRIGGER tf_users_code_seq
>BEFORE INSERT
>ON public.users
>FOR EACH ROW
>EXECUTE PROCEDURE users_code_seq();

Re: [GENERAL] Debian and Postgres

[Adrian Klaver]

On 05/05/2016 01:40 PM, rob stone wrote:

Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote:

On 5/4/2016 1:55 PM, rob stone wrote:

I can connect via psql and issue queries without any problems.
Trying
to connect via JDBC fails. Trying to connect by an application
fails.


one potential difference, psql will connect via a unix domain socket
if you  don't specify a -h hostname, while JDBC can only connect via
a tcp socket.  jdbc connecting to localhost will match `host`
lines in the pg_hba.conf file, while psql connecting without a host
specification will match `local` line(s).

so, please show us your jdbc connection string, and your psql command
line, and also show us your pg_hba.conf file.



Yes, I'm aware of the difference.
The pg_hba.conf file has a date of Feb 24, 2016. This problem occurred
due to me deleting packages via synaptic a few days ago.
I've also just discovered that the resolv.conf file was wacked. So it
has to have something to do with the network stack. No clues in syslog,
just the message in the Postgres log about not starting autovacuum when
it is supposed to start that process and always has up until now.


Exactly. Showing the list the error you get when you cannot connect help 
may with solving that problem and save you a great of time. What have 
you got to lose?




Cheers,
rob





--
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] Function PostgreSQL 9.2

[David G. Johnston]

On Thu, May 5, 2016 at 1:22 PM, drum.lu...@gmail.com 
wrote:

>
>
> On 6 May 2016 at 02:29, David G. Johnston 
> wrote:
>
>> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys 
>> wrote:
>>
>>>
>>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote:
>>>
>>> > The final function code is:
>>> >
>>> > CREATE OR REPLACE FUNCTION users_code_seq()
>>> >RETURNS "trigger" AS $$
>>> > DECLARE code character varying;
>>> > BEGIN
>>> > IF NEW.code IS NULL THEN
>>> > SELECT client_code_increment INTO STRICT NEW.code FROM
>>> public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
>>>
>>>
>>> ^^^
>>> There's your problem. I'm pretty sure the keyword STRICT isn't valid
>>> there. It probably gets interpreted as a column name.
>>>
>>>
>> ​No, its a sanity check/assertion.  If that trips its because there is no
>> company having a value of NEW.id on the public.companies table.  If that is
>> OK then remove the STRICT but if you are indeed expecting a record to be
>> present and it is not it is correctly telling you that there is a problem
>> in the data.  Namely that said company needs to be added to the table.
>>
>> David J.​
>>
>>
>
>
> Taking off the "STRICT", the errors were gone. But still, it's not
> working. Please have a look below.
>

​So, the error messages are gone - the underlying error still exists.​




> If I use the other table:
>
> CREATE TABLE public.company_seqs
>> (company_id BIGINT NOT NULL,
>> last_seq BIGINT NOT NULL DEFAULT 1000,
>> CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
>> );
>
>
> It works fine.. the problem is when I try to use the companies table..
> which is already there and I just add another column
> named: client_code_increment
>
> haven't found the problem yet...
>

​You make this hard to help without a fully self-contained example for
people to read.

​Berend already identified the problem for you.

1) You attached users_code_seq() to a trigger on the users table.
2) You have a where clause:  company_id = NEW.id
3) NEW refers to users
4) NEW.id is obstensibly a USER ID
5) So you are basically saying: WHERE company_id = user_id
6) If you were to get match it would be entirely by accident - say because
you used the same integer for both id values

Hope that helps.

David J.

Re: [GENERAL] Debian and Postgres

[rob stone]

Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote:
> On 5/4/2016 1:55 PM, rob stone wrote:
> > I can connect via psql and issue queries without any problems.
> > Trying
> > to connect via JDBC fails. Trying to connect by an application
> > fails.
>  
> one potential difference, psql will connect via a unix domain socket
> if you  don't specify a -h hostname, while JDBC can only connect via
> a tcp socket.  jdbc connecting to localhost will match `host`
> lines in the pg_hba.conf file, while psql connecting without a host
> specification will match `local` line(s).
> 
> so, please show us your jdbc connection string, and your psql command
> line, and also show us your pg_hba.conf file.
> 

Yes, I'm aware of the difference.
The pg_hba.conf file has a date of Feb 24, 2016. This problem occurred
due to me deleting packages via synaptic a few days ago.
I've also just discovered that the resolv.conf file was wacked. So it
has to have something to do with the network stack. No clues in syslog,
just the message in the Postgres log about not starting autovacuum when
it is supposed to start that process and always has up until now.

Cheers,
rob 


-- 
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] Debian and Postgres

[Adrian Klaver]

On 05/05/2016 01:29 PM, rob stone wrote:

Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote:


So Debian does not rotate the logs into history.log..gz?



Yes, it does! Didn't realise it. You learn something every day.

23 packages removed and 31 purged.

Going thru the list slowly.


Before you go all the way through, can you answer the question posed by 
myself and others:


What connection parameters work(psql) versus those that do not(JDBC, 
application)?


Also try using psql with the parameters that do not work and report back 
the error message if any. The results will probably help narrow the list 
of suspects.




Thanks,
Rob




--
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] Debian and Postgres

[rob stone]

Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote:
> 
> So Debian does not rotate the logs into history.log..gz?
> 

Yes, it does! Didn't realise it. You learn something every day.

23 packages removed and 31 purged.

Going thru the list slowly.

Thanks,
Rob



-- 
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] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

On 6 May 2016 at 02:29, David G. Johnston 
wrote:

> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys  wrote:
>
>>
>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote:
>>
>> > The final function code is:
>> >
>> > CREATE OR REPLACE FUNCTION users_code_seq()
>> >RETURNS "trigger" AS $$
>> > DECLARE code character varying;
>> > BEGIN
>> > IF NEW.code IS NULL THEN
>> > SELECT client_code_increment INTO STRICT NEW.code FROM
>> public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
>>
>>
>> ^^^
>> There's your problem. I'm pretty sure the keyword STRICT isn't valid
>> there. It probably gets interpreted as a column name.
>>
>>
> ​No, its a sanity check/assertion.  If that trips its because there is no
> company having a value of NEW.id on the public.companies table.  If that is
> OK then remove the STRICT but if you are indeed expecting a record to be
> present and it is not it is correctly telling you that there is a problem
> in the data.  Namely that said company needs to be added to the table.
>
> David J.​
>
>


Taking off the "STRICT", the errors were gone. But still, it's not working.
Please have a look below.


If I use the other table:

CREATE TABLE public.company_seqs
> (company_id BIGINT NOT NULL,
> last_seq BIGINT NOT NULL DEFAULT 1000,
> CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
> );


It works fine.. the problem is when I try to use the companies table..
which is already there and I just add another column
named: client_code_increment

haven't found the problem yet...

Re: [GENERAL] psql color hostname prompt

[Steve Crawford]

BTW, I just noticed that as of 9.5 there is an optional GUC called
cluster_name. Unfortunately I don't see a way to reference it in the prompt
string. I'll suggest that as a feature. My earlier hack will work but in
9.5 use cluster_name instead of making up a fake extension variable.

Cheers,
Steve

On Thu, Apr 28, 2016 at 2:41 AM, Francisco Olarte 
wrote:

> Hi Steve:
>
> On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
>  wrote:
> > The various hacks appear to not deal with the fact that there may be
> > multiple instances of postgresql running on different TCP ports or Unix
> > connections nor with the fact that the local connection may, in fact, be
> a
> > pooler and not a direct connection to the database.
>
> Because the problems is with the host, the port is solved trivially
> with %> and the local socket name is dependent on the listening port.
> And, regarding pgbouncer, psql just knows it's talking with someone
> who speaks the postgres protocol, it has no way to know what is being
> done with the socket.
>
> > As long as we're into hack-land, I'll offer one.
> > First, you abuse the custom variables feature in postgresql.conf and add
> > something like:
>
> That's a nice trick ( once you peel of the ; before \gset IIRC ) for
> the problem of knowing which of your server databases you are
> connected to. Low impact and if the database does not have the guc you
> can easily know it ( I think knowing the host is not the problem, it
> does not matter how many socket redirections, bouncers or other things
> you go through your solucion solves the problem ).
>
>
> > Next you update .psqlrc with something along the lines of:
>
> Just a problem, you need it somewhere where it can be re-executed on
> reconnects ( am I the only one who routinely uses \c ? ).
>
> > On the plus side, the custom GUC setting is available to any client, not
> > just psql. It also handles multiple PostgreSQL instances and connections
> > that are actually routed through a pooler.
>
> Yes, you do not know who you are connected to, but you know which
> server istance you end up in, which is nice. I think knowing the
> host/path+port is useful for some things, but for the originally
> stated problem this seems better.
>
> > On the down side, it is a hack. The method is not in any way guaranteed
> to
> > be future-proof. It leaves an ugly bit of server output at psql startup.
> It
> > requires ensuring that all servers have the variable set appropriately.
> You
> > need to avoid colliding with a custom GUC used by an extension. But
> perhaps
> > it is useful.
>
> Not this hacky, I'll use it in preference to changing the prompt with
> scripts ( I'll continue using %M and changing terminal titles, but I'm
> too used to it ).
>
> Well seen.
>
>
> Francisco Olarte.
>

Re: [GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

[George Neuner]

On 5/5/2016 1:17 PM, Moreno Andreo wrote:

Il 05/05/2016 18:40, George Neuner ha scritto:

Otherwise: if Postgresql is loading any non-standard extensions, I
would try to check those DLLs.  If you have a recent Visual Studio
handy, run "link /dump /headers " on the DLLs and look for any
that say "fixed base" under "DLL characteristics".   If you find more
than one that have the same "image base" address, then you've got a
problem.
No extensions here, but I'll give a try. Since I have to do this on 
customer box (without VS) I'll try and find a "smaller package" than a 
VS install...

In this cases it's better to try everything that makes sense... :-)


There's a free utility called "wumpbin" 
(http://www.benf.org/other/wumpbin/) which claims to be a clone of VS 
dumpbin.exe.  AFAICT it works on Win7, but I don't have Win10 available 
to try it there.  And I can't vouch for its accuracy - I have only toyed 
with it.


dumpbin itself appears to be deprecated.  It's still in VS and it still 
works, but  "link /dump ..." is now the preferred method.


George






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

Re: [GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

[George Neuner]

Disclaimer: I do not run Postgresql on Windows.

On Thu, 5 May 2016 14:39:25 +0200, Moreno Andreo
 wrote:

>a strange error is happening to some of our customers.
>They all have a Windows 10 installation on their machines with
>our application and, of course, PostgreSQL 9.1 installed 
>(migration to 9.5 upcoming in late summer/fall, but not applicable
>by now)
>
>:
>
>0xC018 
>
>STATUS_CONFLICTING_ADDRESSES 
>
>{Conflicting Address Range} The specified address range conflicts 
>with the address space. Googling I found many applications failing
>with that error and how to fix them by setting a value in Registry,
>but these are not the cases.
>All I found in common of these machines (except Windows 10 and
>our app :-) ) was ClassicShell. Uninstalling it seemed to resolve the
>problem... until 2 hours ago, when one of them submitted us the
>same crash with same error.
>
>Trying to google deeper did not help for me.
>
>This issue seems to be present on Windows 10 machines.
>
>Any idea/thought/suggestion?

It's a code address conflict.  It's normally caused by trying to load
more than one fixed base DLL at the same address in the same process.

Typically DLLs have a preferred base address, but are relocatable if
that address is already occupied.  DLLs with fixed base addresses
cannot be relocated (the necessary meta-information is not in the
executable).


It is known to have been caused by McAffee and MalwareBytes
Anti-Exploit.  If either of those are installed, they may need to be
updated.


Otherwise: if Postgresql is loading any non-standard extensions, I
would try to check those DLLs.  If you have a recent Visual Studio
handy, run "link /dump /headers " on the DLLs and look for any
that say "fixed base" under "DLL characteristics".   If you find more
than one that have the same "image base" address, then you've got a
problem.

If you don't find anything, then I would guess 9.1 is just too old.

Hope this helps,
George



-- 
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] Possible causes for "tuple concurrently updated" error

[Jacob Scott]

Arg, should have included this in my initial email :-(

9.3.11

On Thu, May 5, 2016 at 7:46 AM, Alvaro Herrera 
wrote:

> On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote:
> > Hi,
> >
> > I'm seeing a "tuple concurrently updated" error thrown while executing
> > UPDATE statements. I've attempted to diligently review previous threads
> on
> > this error (e.g.,
> >
> https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrently+updated+update+site:postgresql.org
> )
> > but am confused about what classes of queries can conflict to cause this
> > error.
>
> What Postgres version are you running?
>
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

[Jacob Scott]

Pinpoint updates of the form

UPDATE $TABLE set field1=$FOO, field2=$BAR ... WHERE pk=$ID


   - All fields are specified
   - Table has no foreign keys (but does have a unmber of indexes)
   - executed as a prepared statement with bind params & data for multiple
   rows provided



On Thu, May 5, 2016 at 8:13 AM, Alvaro Herrera 
wrote:

> Jacob Scott wrote:
> > Arg, should have included this in my initial email :-(
> >
> > 9.3.11
>
> OK.  So what are the updates doing?  Are there any FKs involved?
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

[Alvaro Herrera]

Jacob Scott wrote:
> Arg, should have included this in my initial email :-(
> 
> 9.3.11

OK.  So what are the updates doing?  Are there any FKs involved?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Possible causes for "tuple concurrently updated" error

[Alvaro Herrera]

On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote:
> Hi,
> 
> I'm seeing a "tuple concurrently updated" error thrown while executing
> UPDATE statements. I've attempted to diligently review previous threads on
> this error (e.g.,
> https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrently+updated+update+site:postgresql.org)
> but am confused about what classes of queries can conflict to cause this
> error.

What Postgres version are you running?


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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

[GENERAL] Possible causes for "tuple concurrently updated" error

[Jacob Scott]

Hi,

I'm seeing a "tuple concurrently updated" error thrown while executing
UPDATE statements. I've attempted to diligently review previous threads on
this error (e.g.,
https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrently+updated+update+site:postgresql.org)
but am confused about what classes of queries can conflict to cause this
error.

   - It seems clear from previous threads that
  - two concurrent ANALYZE queries on the same table can cause this
  this error.
  - some access control/DDL statements (e.g. GRANT) run concurrently
  can cause this error.
   - Can a "VACUUM ANALYZE $TABLE" (e.g., from autovacuum) concurrent with
   a "UPDATE $TABLE" cause this error?
   - Can two concurrent "UPDATE $TABLE" queries which touch completely
   disjoint sets of rows (e.g. where clause on primary key with disjoint
   filters) cause  this error?

Thanks,

Jacob

Re: [GENERAL] Function PostgreSQL 9.2

[David G. Johnston]

On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys  wrote:

>
> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote:
>
> > The final function code is:
> >
> > CREATE OR REPLACE FUNCTION users_code_seq()
> >RETURNS "trigger" AS $$
> > DECLARE code character varying;
> > BEGIN
> > IF NEW.code IS NULL THEN
> > SELECT client_code_increment INTO STRICT NEW.code FROM
> public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
>
>
> ^^^
> There's your problem. I'm pretty sure the keyword STRICT isn't valid
> there. It probably gets interpreted as a column name.
>
>
​No, its a sanity check/assertion.  If that trips its because there is no
company having a value of NEW.id on the public.companies table.  If that is
OK then remove the STRICT but if you are indeed expecting a record to be
present and it is not it is correctly telling you that there is a problem
in the data.  Namely that said company needs to be added to the table.

David J.​

[GENERAL] PostgreSQL and Windows 10 exception 0xC0000018

[Moreno Andreo]

  
  
Hi all,
    a strange error is happening to some of our customers.
They all have a Windows 10 installation on their machines with our
application and, of course, PostgreSQL 9.1 installed (migration to
9.5 upcoming in late summer/fall, but not applicable by now)

While working, suddenly PostgreSQL stops working, and log reports

2016-05-05 10:36:19 CEST LOG:  server process (PID 5920) was
terminated by exception 0xC018
2016-05-05 10:36:19 CEST HINT:  See C include file "ntstatus.h" for
a description of the hexadecimal value.
2016-05-05 10:36:19 CEST LOG:  terminating any other active server
processes
2016-05-05 10:36:19 CEST WARNING:  terminating connection because of
crash of another server process
2016-05-05 10:36:19 CEST DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit,
because another server process exited abnormally and possibly
corrupted shared memory.
2016-05-05 10:36:19 CEST HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
[... above three lines repeated a bunch of times...]
2016-05-05 10:36:19 CEST LOG:  all server processes terminated;
reinitializing
2016-05-05 10:36:29 CEST FATAL:  pre-existing shared memory block is
still in use
2016-05-05 10:36:29 CEST HINT:  Check if there are any old server
processes still running, and terminate them.

ntstatus.h refers to exception code as
0xC018
 
STATUS_CONFLICTING_ADDRESSES
 
{Conflicting Address Range} The specified address range
conflicts with the address space.
Googling I found many applications failing with that error and how
to fix them by setting a value in Registry, but these are not the
cases.
All I found in common of these machines (except Windows 10 and our
app :-) ) was ClassicShell. Uninstalling it seemed to resolve the
problem... until 2 hours ago, when one of them submitted us the same
crash with same error.

Trying to google deeper did not help for me.

This issue seems to be present on Windows 10 machines.

Any idea/thought/suggestion?

Thanks in advance,
Moreno.-
  

Re: [GENERAL] Thoughts on "Love Your Database"

[Francisco Olarte]

On Thu, May 5, 2016 at 12:34 AM, Vincent Veyron  wrote:
> I like this quote from Fred Brooks :
>
> `Show me your code and conceal your data structures, and I shall continue to 
> be mystified. Show me your data structures, and I won't usually need your 
> code; it'll be obvious.'

I remembered it as 'Show me your tables,...", which looks even more
applicable in this list. A quick googgle search for SMYT did in fact
return a link to wikiquote at the top, it says "Show me your
flowcharts and conceal your tables, and I shall continue to be
mystified. Show me your tables, and I won’t usually need your
flowcharts; they’ll be obvious. " from TMMM, so its normal I remember
it that way ( I still own it and reread some chunks every couple of
years. )

Francisco Olarte.


-- 
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] Function PostgreSQL 9.2

[Alban Hertroys]

> On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote:

> The final function code is:
> 
> CREATE OR REPLACE FUNCTION users_code_seq()
>RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO STRICT NEW.code FROM 
> public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;

^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It 
probably gets interpreted as a column name.

> END IF;
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.companies SET client_code_increment = 
> (client_code_increment + 1) WHERE id = NEW.id;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
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] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

>
>
>>  SELECT client_code_increment INTO STRICT NEW.code FROM
>> public.companies WHERE id =
>> NEW.id ORDER BY client_code_increment DESC;
>>
>
>
>
> I am pretty sure the above line is wrong. NEW.id refers to users.id, not
> the companies.id. Also, the implementation presents a potential race
> condition, e.g., if two different sessions attempt an insert almost
> simultaneously.


I don't think so..
Even because if I change that to company_id, I get the error:

ERROR:  column "company_id" does not exist



>
>
>
>
>  END IF;
>>  IF (TG_OP = 'INSERT') THEN
>>  UPDATE public.companies SET client_code_increment =
>> (client_code_increment + 1) WHERE
>> id = NEW.id;
>>
>
>
> Ditto w.r.t. NEW.id.
>
>>
>>

Same as above

Re: [GENERAL] Function PostgreSQL 9.2

[Berend Tober]

drum.lu...@gmail.com wrote:

I'm just having some problem when doing:

INSERT INTO public.users

(id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) 
VALUES
(66,'tes...@test.com ','password','0','2016-05-03 
00:01:01','2016-05-03
00:01:01','15');


- see that I'm not providing the "code" column value? If I run the query above, 
I get the following
error:

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function users_code_seq() line 7 at SQL statement


- If I include the code column with a default value:

INSERT INTO public.users

(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
 VALUES
(4,'te...@test.com ','password','0','2016-05-03 
00:01:01','2016-05-03
00:01:01',default,'2');

I get the same error

- Please, if anyone can help with that.. I'd appreciate it.

*The final function code is:*

CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
 IF NEW.code IS NULL THEN
 SELECT client_code_increment INTO STRICT NEW.code FROM 
public.companies WHERE id =
NEW.id ORDER BY client_code_increment DESC;




I am pretty sure the above line is wrong. NEW.id refers to users.id, not the companies.id. Also, the 
implementation presents a potential race condition, e.g., if two different sessions attempt an 
insert almost simultaneously.





 END IF;
 IF (TG_OP = 'INSERT') THEN
 UPDATE public.companies SET client_code_increment = 
(client_code_increment + 1) WHERE
id = NEW.id;



Ditto w.r.t. NEW.id.



 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;





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