AW: CTE with JOIN of two tables is much faster than a regular query

[kpi6288]

> -Ursprüngliche Nachricht-
> Von: Tom Lane 
> Gesendet: Samstag, 18. August 2018 17:29
> 
> In any case, given that the ILIKE selects so few rows (and the planner
knows
> it!), finding a way to index that is clearly the right answer.

A trigram index took 9 minutes to build but improved the regular query from
67 seconds down to 500 ms. 

Although this is an impressive improvement, I'm afraid that the index might
create a delays in other parts of the application (INSERT / UPDATE). We will
probably rework the design of this particular table. 

Thanks to everyone who helped me in this matter. 

Regards 

Klaus 

AW: AW: CTE with JOIN of two tables is much faster than a regular query

[kpi6288]

> -Ursprüngliche Nachricht-
> Von: Tim Cross 
> Gesendet: Sonntag, 19. August 2018 04:57
> >
> > We're using object mapping / entity frameworks (e.g. XPO, Entity
> Framework Core). These frameworks support regular queries out-of-the
> box; a CTEs require additional effort and are more difficult to maintain.
> >
> 
> Ah, another reason to avoid object mapping/entity frameworks! I guess
> really the same reason - loss of flexibility and expressive power.

While I agree that you loose control over certain details, we are overall quite 
happy using the frameworks. 

The frameworks nowadays provide the ability to call procedures if required - 
but using the objects directly is more convenient for the developers. SQL 
procedures add (just like a CTE) an additional layer to the application design 
which needs maintenance. That's fine if it really helps overall but we try to 
avoid it if it isn't needed. 

Regards
Klaus

Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

Hello,

*I have this code for my C extension function.
*

xtern "C" { // C Headers must be inside exter "C" { } block.
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 

PG_MODULE_MAGIC;
}

// CPP Header must be outside extern "C" { } block.
#include 
#include 
#include 
#include 
#include 
#include  // For the ostream_iterator

// External projects c++ libraries compiled and linked on running 'make'.
#include 
#include 
#include 

std::stringstream dec(std::string st){

// Decode the base64 string into a stringstream 
auto decodeBase64 = cppcodec::base64_rfc4648::decode(st);
std::stringstream decodeBase64SS;
std::move(decodeBase64.begin(), decodeBase64.end(),
std::ostream_iterator(decodeBase64SS));

return decodeBase64SS;
}

std::string enc(std::string st){

// Create a vector to hold the raw data
std::vector encodeStream;

// Push all the characters from the raw data string into the vector
for (auto &ch : st){
encodeStream.push_back((unsigned char&&)(ch));
}

// Encode the vector as base64 string
std::string encodeBase64 =
cppcodec::base64_rfc4648::encode(encodeStream);
encodeStream.clear();
return encodeBase64;

}

std::string seal_diff_operation(std::string decodedLocalEncParamTmp,
std::string decodedLocalTmp1, std::string decodedLocalTmp2){

std::stringstream decodedLocalEncParam;
decodedLocalEncParam.str(decodedLocalEncParamTmp);
std::stringstream decodedLocalT1;
decodedLocalT1.str(decodedLocalTmp1);
std::stringstream decodedLocalT2;
decodedLocalT2.str(decodedLocalTmp2);

// Execute seal library operations
// Load the ecryption parameters
seal::EncryptionParameters IntegerEncryptorParms;
IntegerEncryptorParms.load(decodedLocalEncParam);
// Set Context and evaluator objects
seal::SEALContext context(IntegerEncryptorParms);
seal::Evaluator evaluator(context);
// Set the Encoder parameters
seal::IntegerEncoder encoder(context.plain_modulus());

// Create Ciphertexts and load Chipertext information into them
seal::Ciphertext number1Encoded;
seal::Ciphertext number2Encoded;
seal::Ciphertext diffEncodedResult;
number1Encoded.load(decodedLocalT1);
number2Encoded.load(decodedLocalT2);

// Do the diff operation on the Ciphertexts and prepare the result
for output
evaluator.sub(number1Encoded, number2Encoded, diffEncodedResult);
std::stringstream encResult;
diffEncodedResult.save(encResult);

std::string output = enc(encResult.str());
return output;





}

extern "C" { // Usage of CPP functions in the module must be inside extern
"C" { } block. 
Datum seal_diff_cpp(PG_FUNCTION_ARGS){

// Get the inputs
text *t1 = PG_GETARG_TEXT_PP(0);
text *t2 = PG_GETARG_TEXT_PP(1);
text *encParam = PG_GETARG_TEXT_PP(2);
std::string localT1;
std::string localT2;
std::string localEncParam;
localT1 = text_to_cstring(t1);
localT2 = text_to_cstring(t2);
localEncParam = text_to_cstring(encParam);

// Decode the parameters 
std::stringstream decodedLocalT1 = dec(localT1);
std::stringstream decodedLocalT2 = dec(localT2);
std::stringstream decodedLocalEncParam = dec(localEncParam);

// Encode the parameters
std::string encodedLocalT1 = enc(decodedLocalT1.str());
std::string encodedLocalT2 = enc(decodedLocalT2.str());
std::string outputParam =
seal_diff_operation(decodedLocalEncParam.str(), decodedLocalT1.str(),
decodedLocalT2.str());

// Return the result
PG_RETURN_TEXT_P(cstring_to_text_with_len(localT1.c_str(),
localT1.size()));
};

PG_FUNCTION_INFO_V1(seal_diff_cpp);
}


*And I use this Makefile to create the seal_diff_cpp.so file:
*

MODULES = seal_diff_cpp

PG_CONFIG = /usr/pgsql-10/bin/pg_config
PGXS = $(shell $(PG_CONFIG) --pgxs)
INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server)
INCLUDE_SEAL = /usr/local/include/seal
INCLUDE_SEAL_LIB = /usr/local/lib
INCLUDE_CPPCODEC = /usr/local/include/cppcodec
CXX = g++
CXXFLAGS = -std=c++17 -fPIC -Wall -Iinclude -Werror -g -O0 -pthread \
   -I$(INCLUDEDIR) -I$(INCLUDE_SEAL) -I$(INCLUDE_CPPCODEC)
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread
include $(PGXS)
seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
seal_diff_cpp.o $(LDFLAGS)

seal_diff_cpp

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 13:56, TalGloz :
>
> Hello,
>
> *I have this code for my C extension function.
> *
>
> xtern "C" { // C Headers must be inside exter "C" { } block.
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
>
> PG_MODULE_MAGIC;
> }
>
> // CPP Header must be outside extern "C" { } block.
> #include 
> #include 
> #include 
> #include 
> #include 
> #include  // For the ostream_iterator
>
> // External projects c++ libraries compiled and linked on running 'make'.
> #include 
> #include 
> #include 
>
> std::stringstream dec(std::string st){
>
> // Decode the base64 string into a stringstream
> auto decodeBase64 = cppcodec::base64_rfc4648::decode(st);
> std::stringstream decodeBase64SS;
> std::move(decodeBase64.begin(), decodeBase64.end(),
> std::ostream_iterator(decodeBase64SS));
>
> return decodeBase64SS;
> }
>
> std::string enc(std::string st){
>
> // Create a vector to hold the raw data
> std::vector encodeStream;
>
> // Push all the characters from the raw data string into the vector
> for (auto &ch : st){
> encodeStream.push_back((unsigned char&&)(ch));
> }
>
> // Encode the vector as base64 string
> std::string encodeBase64 =
> cppcodec::base64_rfc4648::encode(encodeStream);
> encodeStream.clear();
> return encodeBase64;
>
> }
>
> std::string seal_diff_operation(std::string decodedLocalEncParamTmp,
> std::string decodedLocalTmp1, std::string decodedLocalTmp2){
>
> std::stringstream decodedLocalEncParam;
> decodedLocalEncParam.str(decodedLocalEncParamTmp);
> std::stringstream decodedLocalT1;
> decodedLocalT1.str(decodedLocalTmp1);
> std::stringstream decodedLocalT2;
> decodedLocalT2.str(decodedLocalTmp2);
>
> // Execute seal library operations
> // Load the ecryption parameters
> seal::EncryptionParameters IntegerEncryptorParms;
> IntegerEncryptorParms.load(decodedLocalEncParam);
> // Set Context and evaluator objects
> seal::SEALContext context(IntegerEncryptorParms);
> seal::Evaluator evaluator(context);
> // Set the Encoder parameters
> seal::IntegerEncoder encoder(context.plain_modulus());
>
> // Create Ciphertexts and load Chipertext information into them
> seal::Ciphertext number1Encoded;
> seal::Ciphertext number2Encoded;
> seal::Ciphertext diffEncodedResult;
> number1Encoded.load(decodedLocalT1);
> number2Encoded.load(decodedLocalT2);
>
> // Do the diff operation on the Ciphertexts and prepare the result
> for output
> evaluator.sub(number1Encoded, number2Encoded, diffEncodedResult);
> std::stringstream encResult;
> diffEncodedResult.save(encResult);
>
> std::string output = enc(encResult.str());
> return output;
>
> }
>
> extern "C" { // Usage of CPP functions in the module must be inside extern
> "C" { } block.
> Datum seal_diff_cpp(PG_FUNCTION_ARGS){
>
> // Get the inputs
> text *t1 = PG_GETARG_TEXT_PP(0);
> text *t2 = PG_GETARG_TEXT_PP(1);
> text *encParam = PG_GETARG_TEXT_PP(2);
> std::string localT1;
> std::string localT2;
> std::string localEncParam;
> localT1 = text_to_cstring(t1);
> localT2 = text_to_cstring(t2);
> localEncParam = text_to_cstring(encParam);
>
> // Decode the parameters
> std::stringstream decodedLocalT1 = dec(localT1);
> std::stringstream decodedLocalT2 = dec(localT2);
> std::stringstream decodedLocalEncParam = dec(localEncParam);
>
> // Encode the parameters
> std::string encodedLocalT1 = enc(decodedLocalT1.str());
> std::string encodedLocalT2 = enc(decodedLocalT2.str());
> std::string outputParam =
> seal_diff_operation(decodedLocalEncParam.str(), decodedLocalT1.str(),
> decodedLocalT2.str());
>
> // Return the result
> PG_RETURN_TEXT_P(cstring_to_text_with_len(localT1.c_str(),
> localT1.size()));
> };
>
> PG_FUNCTION_INFO_V1(seal_diff_cpp);
> }
>
>
> *And I use this Makefile to create the seal_diff_cpp.so file:
> *
>
> MODULES = seal_diff_cpp
>
> PG_CONFIG = /usr/pgsql-10/bin/pg_config
> PGXS = $(shell $(PG_CONFIG) --pgxs)
> INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server)
> INCLUDE_SEAL = /usr/local/include/seal
> INCLUDE_SEAL_LIB = /usr/local/lib
> INCLUDE_CPPCODEC = /usr/local/include/cppcodec
> CXX = g++
> CXXFLAGS = -std=c++17 -fPIC -Wall -Iinclude -Werror -g -O0 -pthread \
>-I$(INCLUDEDIR) -I$(INCLUDE_SEAL) -I$(INCLUDE_CPPCODEC)
> LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread
> include $(PGXS)
> seal_diff_cpp.so: seal_diff_cpp.o
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
>
> seal_diff_cpp.o: seal_diff_cpp.cpp
>  $(CXX) $(CXXFL

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

Do you mean this command:

seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
seal_diff_cpp.o $(LDFLAGS)

If yes then the -lseal is added with the $(LDFLAGS) at the end of the
command.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:07, TalGloz :
>
> Do you mean this command:
>
> seal_diff_cpp.so: seal_diff_cpp.o
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
>
> If yes then the -lseal is added with the $(LDFLAGS) at the end of the
> command.
You've defined LDFLAGS as:
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread

I mean it should be defined as:
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread

Also, AFAIK, the command should be specified in one line, like that:
seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
seal_diff_cpp.o $(LDFLAGS)

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

OK, I've changed my line to
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread

In my Makefile this command is written like this (line numbers are just for
orientation)

1: seal_diff_cpp.so: seal_diff_cpp.o
2:$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
seal_diff_cpp.o $(LDFLAGS)
3: 

Or do you mean that it suppose to be like that?

1: seal_diff_cpp.so: seal_diff_cpp.o $(CXX) -Wl,--no-undefined -shared -o
seal_diff_cpp.so  seal_diff_cpp.o $(LDFLAGS)





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:46, TalGloz :
>
> OK, I've changed my line to
> LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread
And? Is these flags appeared in the g++ invoking command line? I mean
the following:

g++ -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
-L/usr/pgsql-10/lib   -L/usr/lib64 -Wl,--as-needed
-Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags

>
> In my Makefile this command is written like this (line numbers are just for
> orientation)
>
> 1: seal_diff_cpp.so: seal_diff_cpp.o
> 2:$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
> 3:
It's correct.

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

No, they still don't appear there and same errors are shown.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:59, TalGloz :
>
> No, they still don't appear there and same errors are shown.
Try to refactor your Makefile like this:
LDFLAGS=-L$(INCLUDE_SEAL_LIB)
LDLIBS=-lseal -lpthread

seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
$(LDFLAGS) $(LDLIBS) seal_diff_cpp.o

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

I'll try it later when I'm home. Is there a reason that you are linking like
this

$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
seal_diff_cpp.o 

And not like this?

$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
$(LDFLAGS) $(LDLIBS)



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 16:20, TalGloz :
>
> I'll try it later when I'm home. Is there a reason that you are linking like
> this
>
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
> seal_diff_cpp.o
>
> And not like this?
>
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
> $(LDFLAGS) $(LDLIBS)
If I recall correctly there were some problems with ordering arguments
of the linker that are specified upon calling g++.
So it is better to specify such arguments in the rest of the command.
But probably this problem is already solved and there is no difference
anymore.

Re: Linker errors while creating a PostgreSQL C extension function.

[Tom Lane]

Dmitry Igrishin  writes:
> вс, 19 авг. 2018 г. в 16:20, TalGloz :
>> I'll try it later when I'm home. Is there a reason that you are linking like
>> this
>> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
>> seal_diff_cpp.o
>> And not like this?
>> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
>> $(LDFLAGS) $(LDLIBS)

> If I recall correctly there were some problems with ordering arguments
> of the linker that are specified upon calling g++.

You're creating such problems, not solving them, if you put the library
first.  Particularly with .a-style libraries, you *must* list the
referencing .o file first, or the linker will deem the library
unreferenced and unnecessary.

None of this explains the failures on the core-backend symbol references,
though.  What platform is this?  I'm suspecting macOS or AIX, which
require the postgres executable to be explicitly mentioned when linking
.so's you intend to be loaded by postgres.  Most other platforms leave
such symbols to be resolved at runtime.

It might be a good idea to see if you can't use PGXS, which will deal
with a lot of these fine points for you.

regards, tom lane

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

I'm using Fedora 28 OS and I'll check it with PGXS. The funny thing is that
everything works when I comment every code line connected to the SEAL
library.

The cppcodec library is also an external library installed to
/usr/local/include/cppcodec like the SEAL library.

Regards,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 17:54, Tom Lane :
>
> Dmitry Igrishin  writes:
> > вс, 19 авг. 2018 г. в 16:20, TalGloz :
> >> I'll try it later when I'm home. Is there a reason that you are linking 
> >> like
> >> this
> >> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
> >> seal_diff_cpp.o
> >> And not like this?
> >> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
> >> $(LDFLAGS) $(LDLIBS)
>
> > If I recall correctly there were some problems with ordering arguments
> > of the linker that are specified upon calling g++.
>
> You're creating such problems, not solving them, if you put the library
> first.  Particularly with .a-style libraries, you *must* list the
> referencing .o file first, or the linker will deem the library
> unreferenced and unnecessary.
Ah, I thought just the opposite :-) ! Since I use CMake I forget this
subtlety. Thank you for pointing this out!

Re: Linker errors while creating a PostgreSQL C extension function.

[Daniel Verite]

TalGloz wrote:

> If yes then the -lseal is added with the $(LDFLAGS) at the end of the
> command.

But it doesn't happen because LDFLAGS is overriden by the
makefile included just after you set it. The relevant part
copy-pasted from your mail:

   LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread
   include $(PGXS)
   seal_diff_cpp.so: seal_diff_cpp.o
  $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
   seal_diff_cpp.o $(LDFLAGS)

As as solution, you could leave LDFLAGS alone and use a different
variable, or include $(PGXS) first and *append* your options to
LDFLAGS if your prefer. 
For instance (replacing -llibseal.a with -lseal while at it):

   SEAL_LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread
   include $(PGXS)
   seal_diff_cpp.so: seal_diff_cpp.o
  $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
   seal_diff_cpp.o $(LDFLAGS) $(SEAL_LDFLAGS)


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

How to create a log file in pg_log directory for each execution of my function.

[Raghavendra Rao J S V]

Hi All,

I have a log file as "
*/opt/postgres/9.2/data/pg_log/postgresql-2018-08-19.csv*".  Due to "
*log_rotation_age=1d*", one log file will be created for me in this pg_log
directory on everyday.

While I am debugging a particular user defined function which contains the
lot of  raise notice  messages , I would like to create a new log file
instead of appending the logs to existing one. How to achieve this?

Like this each and every execution of my function, I wold like to get a new
log file. How to do this.

-- 
Regards,
Raghavendra Rao J S V

Re: How to create a log file in pg_log directory for each execution of my function.

[Adrian Klaver]

On 08/19/2018 10:22 AM, Raghavendra Rao J S V wrote:

Hi All,

I have a log file as " 
*/opt/postgres/9.2/data/pg_log/postgresql-2018-08-19.csv*".  Due to 
"*log_rotation_age=1d*", one log file will be created for me in this 
pg_log directory on everyday.


While I am debugging a particular user defined function which contains 
the lot of raise notice  messages , I would like to create a new log 
file instead of appending the logs to existing one. How to achieve this?


Like this each and every execution of my function, I wold like to get a 
new log file. How to do this.


https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

pg_rotate_logfile() boolean Rotate server's log file



--
Regards,
Raghavendra Rao J S V




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

Re: How to create a log file in pg_log directory for each execution of my function.

[Raghavendra Rao J S V]

Thanks a lot.

On Sun 19 Aug, 2018, 11:09 PM Adrian Klaver, 
wrote:

> On 08/19/2018 10:22 AM, Raghavendra Rao J S V wrote:
> > Hi All,
> >
> > I have a log file as "
> > */opt/postgres/9.2/data/pg_log/postgresql-2018-08-19.csv*".  Due to
> > "*log_rotation_age=1d*", one log file will be created for me in this
> > pg_log directory on everyday.
> >
> > While I am debugging a particular user defined function which contains
> > the lot of raise notice  messages , I would like to create a new log
> > file instead of appending the logs to existing one. How to achieve this?
> >
> > Like this each and every execution of my function, I wold like to get a
> > new log file. How to do this.
>
>
> https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
>
> pg_rotate_logfile() boolean Rotate server's log file
>
> >
> > --
> > Regards,
> > Raghavendra Rao J S V
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

ERROR: cache lookup failed for function 125940

[DrakoRod]

Hi folks!

I've a big problem with a database, is a PostgreSQL 9.6 version on Ubuntu.
When a tried read some tables (approximately 7 of 1073) show this error: 

*ERROR: cache lookup failed for function 125940*

So, I was reading this like data corruption specially the postgresql's
system catalog . I tried generate a dump but in any case, when I try make a
database dump or table's dump show this error: 

*pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
function 406
pg_dump: [archiver (db)] query was: SELECT oid, tableoid, pol.polname,
pol.polcmd, CASE WHEN pol.polroles = '{0}' THEN 'PUBLIC' ELSE
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from
pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual,
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck FROM
pg_catalog.pg_policy pol WHERE polrelid = '129561'*

No matter if this table or table set I can read with a SELECT or not, I
can't run pg_dump in the database show me ever the above error. 

I have idea, of almost all tables I can run a copy to send data to a file,
so I will run copy per table, but just 7 tables a can't read by a select.

Mi questions is: Is there any other way to read this tables and extract the
tuples?, Reading data file directly? omitting some access to catalog? 

Thanks 



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Postgresql

[Sonam Sharma]

I am planning to migrate my db from db2 to postgresql. Before that I wanted
to know is postgresql better than db2? Is it completely free ? Any
performance issues or any limitations?

Re: Postgresql

[Adrian Klaver]

On 08/19/2018 10:53 AM, Sonam Sharma wrote:





I am planning to migrate my db from db2 to postgresql. Before that I 
wanted to know is postgresql better than db2? Is it completely free ? 
Any performance issues or any limitations?


Yes, yes, depends

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

Re: Postgresql

[Sonam Sharma]

Thank you! Can you please help me with any advantages/disadvantages.. my db
size is less than 10gb. I am very new to this.

On Sun, Aug 19, 2018, 11:25 PM Adrian Klaver 
wrote:

> On 08/19/2018 10:53 AM, Sonam Sharma wrote:
> >
> >
> >
> >
> > I am planning to migrate my db from db2 to postgresql. Before that I
> > wanted to know is postgresql better than db2? Is it completely free ?
> > Any performance issues or any limitations?
>
> Yes, yes, depends
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgresql

[Adrian Klaver]

On 08/19/2018 10:56 AM, Sonam Sharma wrote:
Thank you! Can you please help me with any advantages/disadvantages.. my 
db size is less than 10gb. I am very new to this.


I would start here:

https://www.postgresql.org/about/



On Sun, Aug 19, 2018, 11:25 PM Adrian Klaver > wrote:


On 08/19/2018 10:53 AM, Sonam Sharma wrote:
 >
 >
 >
 >
 > I am planning to migrate my db from db2 to postgresql. Before that I
 > wanted to know is postgresql better than db2? Is it completely
free ?
 > Any performance issues or any limitations?

Yes, yes, depends

-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Postgresql

[Christophe Pettus]

> On Aug 19, 2018, at 10:56, Sonam Sharma  wrote:
> 
> Thank you! Can you please help me with any advantages/disadvantages.. my db 
> size is less than 10gb. I am very new to this.

That's a topic far too broad for a simple mailing list thread.  PostgreSQL is 
extremely feature-complete, but how it performs on any particular database will 
vary considerably.

The good news is that for a database of that size, it's easy to install it and 
test it against your workload.  That's really the only practical way of telling 
if it will be suitable for you.

--
-- Christophe Pettus
   x...@thebuild.com

Re: ERROR: cache lookup failed for function 125940

[Tom Lane]

DrakoRod  writes:
> I've a big problem with a database, is a PostgreSQL 9.6 version on Ubuntu.
> When a tried read some tables (approximately 7 of 1073) show this error: 

> *ERROR: cache lookup failed for function 125940*

> So, I was reading this like data corruption specially the postgresql's
> system catalog . I tried generate a dump but in any case, when I try make a
> database dump or table's dump show this error: 

> *pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
> function 406

Hmm, perhaps reindexing pg_proc would get you out of trouble?

Also, if that isn't 9.6.10, I'd suggest updating --- we have fixed
some data corruption issues since 9.6.0.

regards, tom lane

Re: Postgresql

[Chris Travers]

On Sun, Aug 19, 2018 at 7:57 PM Sonam Sharma  wrote:

> Thank you! Can you please help me with any advantages/disadvantages.. my
> db size is less than 10gb. I am very new to this.
>

At 10GB you are unlikely to hit performance limitations that make you think
about how to use PostgreSQL more effectively.  You will have to avoid doing
the sorts of things you want to avoid doing on all databases.

DB2 and PostgreSQL are both very feature-rich databases but in somewhat
different directions.   For example, DB2 has more storage options than
PostgreSQL does.   But again at 10GB it is unlikely you will have to worry
about these.

Where PostgreSQL really shines is in generally workload tuning and
programmability.  Especially programmability.  PostgreSQL is extremely
extensible and this means not only can you build your own extensions for
whatever you need to do (assuming some skill) but there are tons of
extensions you can use for free including great ones like PostGIS.

Generally you can expect at 10GB to have to avoid needless complexity like
repeated sequential scans. Indexing becomes a bit important.  By 100GB you
have to pay significant attention to index strategies and disk access.  I
am aware of single databases of up to 50TB in size used in production and
federated storage environments into the petabytes.

In general I can think of no reason not to use PostgreSQL unless your
salary depends on paying license fees

>
> On Sun, Aug 19, 2018, 11:25 PM Adrian Klaver 
> wrote:
>
>> On 08/19/2018 10:53 AM, Sonam Sharma wrote:
>> >
>> >
>> >
>> >
>> > I am planning to migrate my db from db2 to postgresql. Before that I
>> > wanted to know is postgresql better than db2? Is it completely free ?
>> > Any performance issues or any limitations?
>>
>> Yes, yes, depends
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Linker errors while creating a PostgreSQL C extension function.

[TalGloz]

Thanks, that did the trick. But now I'm getting this
g++ -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
-L/usr/pgsql-10/lib   -L/usr/lib64 -Wl,--as-needed
-Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags -L/usr/local/lib -lseal
-pthread
/usr/bin/ld: /usr/lib64/libseal.a(bigpoly.cpp.o): relocation R_X86_64_32
against `.rodata.str1.1' can not be used when making a shared object;
recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(biguint.cpp.o): relocation R_X86_64_32
against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(ciphertext.cpp.o): relocation R_X86_64_32
against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(context.cpp.o): relocation R_X86_64_32
against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(encoder.cpp.o): relocation R_X86_64_32S
against symbol `_ZTVN4seal14IntegerEncoderE' can not be used when making a
shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(encryptionparams.cpp.o): relocation
R_X86_64_32 against `.rodata.str1.1' can not be used when making a shared
object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(evaluator.cpp.o): relocation R_X86_64_32
against `.rodata.str1.8' can not be used when making a shared object;
recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(plaintext.cpp.o): relocation R_X86_64_32
against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(randomgen.cpp.o): relocation R_X86_64_32S
against symbol `_ZTVN4seal21StandardRandomAdapterISt13random_deviceEE' can
not be used when making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(smallmodulus.cpp.o): relocation
R_X86_64_32 against `.rodata.str1.1' can not be used when making a shared
object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(baseconverter.cpp.o): relocation
R_X86_64_32 against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be
used when making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(globals.cpp.o): relocation R_X86_64_32
against `.bss' can not be used when making a shared object; recompile with
-fPIC
/usr/bin/ld: /usr/lib64/libseal.a(hash.cpp.o): relocation R_X86_64_32
against symbol `_ZN4seal4util12HashFunction17sha3_round_constsE' can not be
used when making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(mempool.cpp.o): relocation R_X86_64_32S
against symbol `_ZTVN4seal4util12MemoryPoolSTE' can not be used when making
a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(modulus.cpp.o): relocation R_X86_64_32
against symbol `__gxx_personality_v0@@CXXABI_1.3' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(smallntt.cpp.o): relocation R_X86_64_32
against symbol `__pthread_key_create@@GLIBC_2.2.5' can not be used when
making a shared object; recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(uintarith.cpp.o): relocation R_X86_64_32S
against `.rodata' can not be used when making a shared object; recompile
with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(uintarithsmallmod.cpp.o): relocation
R_X86_64_32 against `.bss' can not be used when making a shared object;
recompile with -fPIC
/usr/bin/ld: /usr/lib64/libseal.a(uintcore.cpp.o): relocation R_X86_64_32
against `.rodata.str1.1' can not be used when making a shared object;
recompile with -fPIC

Do I have to replace my -shared in the link command with -fPIC?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: ERROR: cache lookup failed for function 125940

[DrakoRod]

Hi Tom

I reindex the pg_proc table and reindex finished correctly but I try read
the tables or make the dump and same error, reindexed the database and show
me this error: 

ERROR:  cache lookup failed for function 125999

Any suggestions, If I run pg_upgrade to 10, will these errors be corrected? 

Thanks! 



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pg_stat_statements in the query column I am not getting the exact query

[nikhil raj]

Hello
I am using postgres 10.3 version current facing an issue related query
tracking whenever I run SELECT * FROM pg_stat_statements in the query
column I am not getting the exact query with values with variable but
instead I am getting $1, $2 in the query

example- "update ss set number=$1 where number =$2"

How can I get the whole exact query and what changes should I do it?

"update ss set number=245 where number =bill"

Thanks.

Re: Linker errors while creating a PostgreSQL C extension function.

[Tom Lane]

TalGloz  writes:
> Thanks, that did the trick. But now I'm getting this
> /usr/bin/ld: /usr/lib64/libseal.a(bigpoly.cpp.o): relocation R_X86_64_32
> against `.rodata.str1.1' can not be used when making a shared object;
> recompile with -fPIC

Ugh, I was wondering if that was really going to work or not.  Your copy
of libseal.a has been built without relocation capability, so it can't
be included in a .so library.

In principle, if you have the source code for libseal, you could recompile
it with -fpic or -fPIC and continue to link against the .a form of the
library.  But it might be better to turn it into a .so in its own right.

regards, tom lane

Re: ERROR: cache lookup failed for function 125940

[Tom Lane]

DrakoRod  writes:
> I reindex the pg_proc table and reindex finished correctly but I try read
> the tables or make the dump and same error, reindexed the database and show
> me this error: 

> ERROR:  cache lookup failed for function 125999

This suggests you've actually lost some entries from pg_proc.  Have
you had any system crashes or suchlike?

> Any suggestions, If I run pg_upgrade to 10, will these errors be corrected? 

No, pg_upgrade can't magically restore data that's not there.

regards, tom lane

Re: ERROR: cache lookup failed for function 125940

[DrakoRod]

>This suggests you've actually lost some entries from pg_proc.  Have
>you had any system crashes or suchlike?

yes two crashes for power outages, this guys doesn't have dumps recent, only
to 2 days ago, I try recover the much data as possible. 

>No, pg_upgrade can't magically restore data that's not there.

I had a little hope that this possibility will work, but I understand that
is impossible after 2 crashes like those. 

Another option? to recover something? 

Thanks! 
DrakoRod



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: [External] Re: ERROR: cache lookup failed for function 125940

[Vijaykumar Jain]

Hey Tom,

Had a small query here.
If we have streaming replication enabled, and if we have corruption like this 
due to power loss etc, 
is it safe to assume we failover to standby  and we should be good (atleast in 
most cases)
I wanted to understand if the system catalog is corrupted, for some reason, 
will it/will it not  stream replicate the corrupted data to standby.
Or they are a property of an individual database server and will be 
rebuilt/updated on pg_dump/restore.

I am sorry if I am diverting the Drako's query or if that is not related, I'll 
ask another question then __
But I guess maybe the answer may help Drako too.

Thanks,
Vijay

On 8/20/18, 12:53 AM, "Tom Lane"  wrote:

DrakoRod  writes:
> I reindex the pg_proc table and reindex finished correctly but I try read
> the tables or make the dump and same error, reindexed the database and 
show
> me this error: 

> ERROR:  cache lookup failed for function 125999

This suggests you've actually lost some entries from pg_proc.  Have
you had any system crashes or suchlike?

> Any suggestions, If I run pg_upgrade to 10, will these errors be 
corrected? 

No, pg_upgrade can't magically restore data that's not there.

regards, tom lane

Re: pg_stat_statements in the query column I am not getting the exact query

[legrand legrand]

Hi,
This is the expected behavior, like that sql statements with différent
values are shared in pg_stat_statements ...
There is no parameter to change this, if you really want query paramèters
you néed to have a look at statements logging, but be carrefull this can
generate a huge volume of log files depending on your  workload ...

See log_statements at
https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Audit management

[Achilleas Mantzios]

On 10/08/2018 21:20, dangal wrote:

Achilleas thanks for your answer
A query, the only possible way out is the postgres log?
I would like to be able to throw the audit output to a different place so
that I can not access the file

Yes, only the postgresql log, and you gotta take care of ^M (CR), CRLF in the 
data.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Allow Reg Expressions in Position function

[Nick Dro]

Position function allows only string as pattern parameter:
 
select position(sub-string in string)
 
If you are looking for more than one sub-string you can't do it with a single call to position.
More info: 
https://stackoverflow.com/questions/51925037/how-to-find-position-of-multiple-sub-string-in-postgresql
 
 
Required solution: make sub-string accept regular _expression_ just like the LIKE operator.