Picon

Building extensions against OpenSCG RPM packages

Hello list,
 
What are your experiences with OpenSCG’s RPM packages?  It is my impression that those packages allow vanilla PostgreSQL to run, but trying to build extensions such as PostGIS against them fails in most (two out of three) cases due to problems with the included shared libraries.
 
The “two out of three cases” means that I tried three of their packages, then basically gave up on those OpenSCG packages as one “builds out of the box” success out of three seemed a bit on the low side.  Is that “success rate” about correct, or could I have picked the only two packages with such problems?
 
Of course, OpenSCG’s “selling points” (packages have been relocatable since around 2011, and are largely independent of the Linux distribution due to extra libraries supplied) did sound good, so you might still consider OpenSCG’s packages if you just want to run “vanilla” PostgreSQL.
 
As I mentioned, in one case building PostGIS against the installed PostgreSQL worked out of the box;  in one case building a PostGIS extension didn’t work against the libraries supplied by OpenSCG, but after copying around some system libraries things both built and ran fine;  one case was even weirder in that an initial build succeeded but produced a shared library that would error out at run time, and copying over some system libraries resulted in a state in which the build succeeded AND produced a working shared library (see the earlier discussion about that weird case:  http://www.postgresql.org/message-id/C5DBACC6DCC7604C9E4875FD9C7968B1129DF47A16 <at> ITXS01EVS.service.it.nrw.de;  it was compounded by the problem that just copying over just one system library didn’t work at all, and as it turned out, I also needed to copy over a dependency).
 
Figuring out which system libraries to copy over can be sort of fun if you have a little development background, but database administrators may shy away from copying bunches of shared libraries around.  What could be going wrong here?  How can a shared library allow things to run fine but prevent things from building against it?
 
Holger Friedrich
 
 
 
sri harsha | 29 Jan 06:41 2015
Picon

How does Delete Query work ??

Hi,


   I want to know about the flow in which delete query is executed in postgres .
Assume i have a following query.

Delete from table_a * where column_a = 'something' 

How is this query executed ? Are the rows first filtered out and then deleted one by one ?? Or is the row deleted as and when it is filtered ??

Thanks,
Harsha

Anand Kumar, Karthik | 28 Jan 21:39 2015

vacuum full post 9.0 - reindex needed?

I haven’t been able to find clear information online about what happens with existing indexes on a postgres post-9.0 database (we specifically use 9.3) after a vacuum full.

There is a lot of information on how a vacuum differs from a vacuum full, but my question is – is a re-index needed after a full vacuum? 

We’ve seen from practice that indexes do work after vacuum fulls, but – will a reindex make them more optimal?

~Karthik
Adam Mackler | 28 Jan 05:06 2015

Conflicting function name in dynamically-loaded shared library

Hi:

I am wanting to define some functions as described in section 35.9 of
the manual, "C-Language Functions."  I am compiling pre-existing files
of c-code from another project into object files, and then linking
those object files into a shared library along with my own functions
that follow the posgresql calling convention, invoking other functions
as desired.

This pre-existing c-code I'm compiling happens to contain a function
named "point_add()".  I see the postgresql source file fmgrtab.c also
has a function with the same name.  When my code tries to invoke its
version of point_add() I get a bus error, and when I changed the name
of that function to something else the bus error went away.

Of course, since I'm working with source code I can just keep the
modified function name, but I would like to be able to keep my version
of this c code updated with that project and so to use it unmodified.

So my questions are: first, might I be wrong about the cause of this
bus error?  I cannot think of another reason why changing the name of
a function would have this effect, but maybe there's some other reason
besides the "point_add()" function in fmgrtab.c conflicting.  If so,
I'm interested to know.

If, however, it is probable that this bus error is a result of this
naming conflict, then do I have any options for working around it that
would enable me to use the code from this other library without
changing the name of its "point_add()" function?  I know I could ask
that project's developers to change the function's name, but that
could break other code that currently uses it, and even if it didn't,
I would prefer something less intrusive on that project.

Thanks very much for any ideas about this,
-- 
Adam Mackler

--

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

David Guyot | 27 Jan 14:29 2015

sslmode verify-ca and verify-full: essentialy the same?

Hi, there.

Firstly, as this is my first post on a PgSQL ML, I hope this ML is the
good one for my question.

I'm trying to secure further some PgSQL servers and am reading
documentation about libpq sslmode option. I have a question about that:
as I understand the internals of this option, the difference between
verify-ca and verify-full is that, for verify-full, client will compare
the hostname the server gave and the one in the SSL certificate, and
will give up if these two values differ. Am I right up to now?

If I'm right, I feel like the extra security of verify-full compared to
verify-ca is merely a smoke screen because, as far as I know, nothing
prevents a crafted server to read the certificate's hostname and give
this one as its own, and the libpq shouldn't show a better MitM
protection with verify-full than with verify-ca. If I'm wrong, where am
I wrong? How does libpq verify the server's name? Reverse DNS? Other
mean?

Hoping someone can enlighten me about this,

Regards.
--

-- 
David Guyot
Administrateur système, réseau et télécom / Sysadmin
Europe Camions Interactive / Stockway
Moulin Collot
F-88500 Ambacourt
03 29 30 47 85
Andreas Laggner | 27 Jan 12:42 2015
Picon

cannot start 9.3 after system crash

Hi list,

the system HDD of a server running postgresql 9.3 died (sudden 
electronical death) during a pg_dump. The database is located on a 
hardware raid system (another HDD). I installed a new ubuntu 14.04 with 
postgresql 9.3 postgis 2.1. But i cannot start the server. First i got 
the error:

2015-01-27 09:37:11 CET   LOG:  database system was shut down at 
2015-01-23 16:37:02 CET
2015-01-27 09:37:11 CET   LOG:  invalid primary checkpoint record
2015-01-27 09:37:11 CET   LOG:  invalid secondary checkpoint record
2015-01-27 09:37:11 CET   PANIC:  could not locate a valid checkpoint record
2015-01-27 09:37:11 CET   LOG:  startup process (PID 5309) was 
terminated by signal 6: Aborted
2015-01-27 09:37:11 CET   LOG:  aborting startup due to startup process 
failure

I solved this by executing pg_resetxlog.

Now i get another error and i do not know what else i can do:

2015-01-27 12:02:22 CET   LOG:  database system was interrupted; last 
known up at 2015-01-27 10:24:56 CET
2015-01-27 12:02:22 CET   LOG:  database system was not properly shut 
down; automatic recovery in progress
2015-01-27 12:02:22 CET   LOG:  record with zero length at 360/E20001C8
2015-01-27 12:02:22 CET   LOG:  redo is not required
2015-01-27 12:02:22 CET [unknown] [unknown] LOG:  incomplete startup packet
2015-01-27 12:02:22 CET   FATAL:  could not access status of transaction 
214707
2015-01-27 12:02:22 CET   DETAIL:  Could not open file "pg_clog/0000": 
Datei oder Verzeichnis nicht gefunden.
2015-01-27 12:02:22 CET   LOG:  startup process (PID 6520) exited with 
exit code 1
2015-01-27 12:02:22 CET   LOG:  aborting startup due to startup process 
failure

thanks for helping  -  Andreas

-- 
Dipl. Geoökologe Andreas Laggner

Thünen-Institut für Agrarklimaschutz (AK)
Arbeitsgruppe Emissionsinventare
Johann Heinrich von Thünen-Institut (vTI),
Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

Thünen Institute of Climate-Smart Agriculture
Johann Heinrich von Thünen Institute (vTI),
Federal Research Institute for Rural Areas, Forestry and Fisheries

Bundesallee 50
D-38116 Braunschweig

Tel.: (+49) (0)531 596 2636
Fax : (+49) (0)531 596 2645
E-mail: andreas.laggner <at> ti.bund.de
Homepage: http://www.ti.bund.de

--

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

Oliver Dizon | 27 Jan 11:46 2015

(unknown)

Hi Guys,

I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.

All records were deleted in a table even if the subquery in the where clause has a missing a column.

--from a certain script where table_id is not yet existing on table_sample

delete from main_table_sample where table_id in (select table_id from table_sample);

Im expecting that postgres will return an ERROR message like
'column "table_id" does not exist on table table_sample'

But it did not returned any error and deleted all the records instead.

Version: psql (8.4.9, server 9.2.5)

Thanks and Regards,
______________________
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoliver <at> rtzassociates.com

--

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

Christian Paminger | 27 Jan 10:19 2015
Picon

Re: multiple parameters to an AGGREGATE function

Hi,

maybe this will help:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer ) RETURNS 
anyelement AS $BODY$ SELECT a FROM unnest( $1 ) a ORDER BY a offset $2 
LIMIT 1; $BODY$

USE it without an extra aggregate-function.

SELECT _final_nth(array_agg(someelement),n) AS someelement FROM 
sometable GROUP BY someelement;

n is the offset.

Greetings ...

On Fri, Feb 3, 2012 at 11:27 AM, Graham 
<graham(dot)stark(at)virtual-worlds(dot)biz> wrote:

Hi,
    first time poster here ...

I'm trying to write a simple Aggregate function which returns the nth
element in a collection - ultimately I want to find 95th, 90th percentiles
and so on.

It'd be called like:

select nth_element( value, 95 ) from something group by ...

I'm basing this on an example I found on the Wiki:

http://wiki.postgresql.org/wiki/Aggregate_Mode

So, I have:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer )
   RETURNS anyelement AS
$BODY$
     SELECT a
     FROM unnest( $1 ) a
     ORDER BY a
     offset $2
     LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE nth_element( anyelement, integer ) (
   SFUNC=array_append,
   STYPE=anyarray,
   FINALFUNC=_final_nth,
   INITCOND='{}'
);

Where the 2nd parameter would be, say 5 for the 5th element an so on.

The function declaration seems fine. But the CREATE AGGREGATE declaration
fails with:

ERROR:  function array_append(anyarray, anyelement, integer) does not exist

so, I suppose it's decided to call array_append with all the parameters on
the command line, rather than just the array in the 1st element. Is there
any way to stop it doing this?

I've searched the online documentation and Googled but haven't found
anything.

thanks,

Graham
x

--

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

Laurence Rowe | 27 Jan 08:51 2015
Picon

Synchronous archiving

I have a relatively small database with not many writes. However, I'm keen to ensure that those writes I receive are not lost. Archiving gets me most of the way there, but it is asynchronous. Is there any way to archive individual WAL records without running a replica?

Running in Amazon's cloud, I imagine a synchronous counterpart to WAL-E that backed up the individual WAL records to one of their reliable but less featureful systems.

Laurence
Tim Smith | 26 Jan 23:38 2015
Picon

Versioning Schema SQL ideas needed

Hi,

I've spent too long staring at code today and am facing a bit of a
block when trying to figure out how to best implement the following.

I'm basically looking at tracking versions of some configuration items
saved in a database table.  My thinking of the table is something
along the following :

create table templates(
   template_id int not null primary key,
   template_groupid int not null,
   template_version int not null
   template_text text not null);

My thinking on the above is :
- template_id is a unique ID for that version
- template_groupid identifies the set the template belongs to
- version is the version

Would I need to resort to using a CTE or subquery to make this sort of
thing work ?  I can't seem to make it work with group by since group
by expects aggregation. Surely I don't need to normalise it into a
couple of tables ?

Sorry if its a silly question !

Tim

--

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

Christophe Pettus | 26 Jan 22:00 2015

Logical decoding output plug-in questions

I'm having a great time playing with logical decoding, and had a couple of parameter questions:

* OutputPluginPrepareWrite, OutputPluginWrite -- last_write.  This is false if you are planning to do
another OutputPluginPrepareWrite/OutputPluginWrite pair in the same invocation of the same callback
function, yes?

* On an UPDATE, it appears that data.tp.oldtuple is only not-NULL if a primary key field changed, and then it
only includes primary key fields (the rest being NULL).  Correct?

* pg_decode_startup -- is_init.  What's the situation under which this is true or false?

Thanks!

--
-- Christophe Pettus
   xof <at> thebuild.com

--

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


Gmane