[GENERAL] error while installing auto_explain contrib module
Hi, I am trying to install auto_explain on PostgreSQL-9.3.11 OS X EI Capitan - 10.11 I am getting following error: -- $ pwd /Users/sachin/postgres_git/postgres/contrib/auto_explain $ make gcc -g -O0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -pg -I. -I. -I../../src/include -I/opt/local/include/libxml2 -c -o auto_explain.o auto_explain.c gcc -g -O0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -pg -bundle -multiply_defined suppress -o auto_explain.so auto_explain.o -L../../src/port -L../../src/common -L/opt/local/lib -Wl,-dead_strip_dylibs -bundle_loader ../../src/backend/postgres ld: warning: directory not found for option '-L/usr/local/lib' Undefined symbols for architecture x86_64: "_MemoryContextSwitchTo", referenced from: _explain_ExecutorStart in auto_explain.o ld: symbol(s) not found for architecture x86_64 clang: error: linker command failed with exit code 1 (use -v to see invocation) make: *** [auto_explain.so] Error 1 -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] error while installing auto_explain contrib module
On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver wrote: > On 04/18/2016 11:46 PM, Sachin Kotwal wrote: > >> Hi, >> >> I am trying to install auto_explain on PostgreSQL-9.3.11 >> OS X EI Capitan - 10.11 >> > > Did you install the 9.3.11 server from source also? Yes. I have clone git repository and checkout for pg-9.3.11 > > >> >> I am getting following error: >> >> -- >> $ pwd >> /Users/sachin/postgres_git/postgres/contrib/auto_explain >> > > What version of Postgres is the above pointing to? This is my postgres git repository and i have checkout for pg-9.3.11 So above is pointing to auto_explain directory under contrib for same postgres version. Here is my pg_config output: - $ pg_config BINDIR = /Users/sachin/pg_git_install/9.3.11/bin DOCDIR = /Users/sachin/pg_git_install/9.3.11/share/doc/postgresql HTMLDIR = /Users/sachin/pg_git_install/9.3.11/share/doc/postgresql INCLUDEDIR = /Users/sachin/pg_git_install/9.3.11/include PKGINCLUDEDIR = /Users/sachin/pg_git_install/9.3.11/include/postgresql INCLUDEDIR-SERVER = /Users/sachin/pg_git_install/9.3.11/include/postgresql/server LIBDIR = /Users/sachin/pg_git_install/9.3.11/lib PKGLIBDIR = /Users/sachin/pg_git_install/9.3.11/lib/postgresql LOCALEDIR = /Users/sachin/pg_git_install/9.3.11/share/locale MANDIR = /Users/sachin/pg_git_install/9.3.11/share/man SHAREDIR = /Users/sachin/pg_git_install/9.3.11/share/postgresql SYSCONFDIR = /Users/sachin/pg_git_install/9.3.11/etc/postgresql PGXS = /Users/sachin/pg_git_install/9.3.11/lib/postgresql/pgxs/src/makefiles/ pgxs.mk CONFIGURE = '--with-libxml' '--enable-cassert' '--enable-debug' '--enable-profiling' '--prefix=/Users/sachin/pg_git_install/9.3.11/' '--with-python' 'CFLAGS=-g -O0' CC = gcc CPPFLAGS = -I/usr/include/libxml2 CFLAGS = -g -O0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -pg CFLAGS_SL = LDFLAGS = -L../../../src/common -Wl,-dead_strip_dylibs LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgport -lpgcommon -lxml2 -lz -lreadline -lm VERSION = PostgreSQL 9.3.11 > > >> $ make >> gcc -g -O0 -Wall -Wmissing-prototypes -Wpointer-arith >> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute >> -Wformat-security -fno-strict-aliasing -fwrapv >> -Wno-unused-command-line-argument -g -pg -I. -I. -I../../src/include >> -I/opt/local/include/libxml2 -c -o auto_explain.o auto_explain.c >> gcc -g -O0 -Wall -Wmissing-prototypes -Wpointer-arith >> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute >> -Wformat-security -fno-strict-aliasing -fwrapv >> -Wno-unused-command-line-argument -g -pg -bundle -multiply_defined >> suppress -o auto_explain.so auto_explain.o -L../../src/port >> -L../../src/common -L/opt/local/lib -Wl,-dead_strip_dylibs >> -bundle_loader ../../src/backend/postgres >> ld: warning: directory not found for option '-L/usr/local/lib' >> Undefined symbols for architecture x86_64: >>"_MemoryContextSwitchTo", referenced from: >>_explain_ExecutorStart in auto_explain.o >> ld: symbol(s) not found for architecture x86_64 >> clang: error: linker command failed with exit code 1 (use -v to see >> invocation) >> make: *** [auto_explain.so] Error 1 >> >> >> -- >> >> Thanks and Regards, >> Sachin Kotwal >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] error while installing auto_explain contrib module
Hi Tom, Thanks for reply. On Tue, Apr 19, 2016 at 7:40 PM, Tom Lane wrote: > Sachin Kotwal writes: > > On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver < > adrian.kla...@aklaver.com> > > wrote: > >> Did you install the 9.3.11 server from source also? > > > Yes. I have clone git repository and checkout for pg-9.3.11 > > The reason for that question is that the most obvious explanation for this > failure is that the auto_explain build is seeing a different value for > PG_USE_INLINE than the main server build did. Ok. But I am not getting getting why auto_explain is doing that. All pg_config parameters have correct value as per my installation posted in last mail. Is it correct behaviour of auto_explain or it is bug ? > So probably your include > path is picking up a pg_config.h that does not match the postgres > executable the link is happening against. > > regards, tom lane > If pg_config showing following as INCLUDEDIR path INCLUDEDIR = /Users/sachin/pg_git_install/9.3.11/include How it can take pg_config.h from different include path? Can you tell me where i should look in auto_explain to fix this? -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] error while installing auto_explain contrib module
On Thu, Apr 21, 2016 at 7:20 PM, Adrian Klaver wrote: > On 04/21/2016 02:23 AM, Sachin Kotwal wrote: > >> Hi Tom, >> >> Thanks for reply. >> >> On Tue, Apr 19, 2016 at 7:40 PM, Tom Lane > <mailto:t...@sss.pgh.pa.us>> wrote: >> >> Sachin Kotwal mailto:kotsac...@gmail.com>> >> writes: >> > On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver < >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> >> > wrote: >> >> Did you install the 9.3.11 server from source also? >> >> > Yes. I have clone git repository and checkout for pg-9.3.11 >> >> The reason for that question is that the most obvious explanation >> for this >> failure is that the auto_explain build is seeing a different value for >> PG_USE_INLINE than the main server build did. >> >> >> Ok. But I am not getting getting why auto_explain is doing that. >> All pg_config parameters have correct value as per my installation >> posted in last mail. >> > > Do you have a Postgres instance installed that did not come from the Git > clone? Currently I do not have Postgres installation from other than Git but i will try that soon. I did same Postgres version installation of on CentOS 6 , It is working fine. I think it is problem with OS X. On OS X contrib tools unable to find correct installation paths. I feel we need to fix them. >> Is it correct behaviour of auto_explain or it is bug ? >> >> So probably your include >> path is picking up a pg_config.h that does not match the postgres >> executable the link is happening against. >> >> regards, tom lane >> >> >> If pg_config showing following as INCLUDEDIR path >> >> INCLUDEDIR = /Users/sachin/pg_git_install/9.3.11/include >> >> >> How it can take pg_config.h from different include path? >> >> Can you tell me where i should look in auto_explain to fix this? >> >> >> -- >> >> Thanks and Regards, >> Sachin Kotwal >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )
Hi, > > * >> * >> *While trying to drop a column by replacing view definition from view it >> throws an error saying cannot drop column from view.* >> *=* >> postgres=# create or replace view vi1 as select >> id , name from orgdata ; >> *ERROR: cannot drop columns from view* >> > > You need to drop the view before recreating it. Then it works. If you > changed the access to the view with grants or revokes, you also neet to > recreate them. They are dropped with the view. > > Sorry to say but If we need to drop and replace then what is use of "Create OR Replace " syntax? > If its not a bug and a limitation kindly guide me towards any >> documentation where it is mentioned. >> > > http://www.postgresql.org/docs/current/static/sql-createview.html > > CREATE OR REPLACE VIEW is similar, but if a view of the same name already > exists, it is replaced. The new query must generate the same columns that > were generated by the existing view query (that is, the same column names > in the same order and with the same data types), but it may add additional > columns to the end of the list. The calculations giving rise to the output > columns may be completely different. > > If this is the limitation. Is community is planning update this or add this feature soon? On Mon, May 16, 2016 at 12:50 PM, Francisco Olarte wrote: > On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende > wrote: > > While working on the view I came across an unusual behaviour of the view, > > PostgreSQL do not allows to drop a column from the view, whereas same > > pattern of Create and Replace view works while adding a column. > > This is probably because you are using create or replace, which is > normally used to switch things in place and so it needs them to be > compatible with the old ones. A view with an extra column can be used > instead of the old one, but a view with less columns can not. I do not > see the 'not dropping' part as unusual, and the 'can add columns', > well, I see them as a little unusual on a create or replace but I see > the point in hallowing it, so just a little. > > > Alter command do not have any option to drop column > > postgres=# alter view vi1 > > ALTER COLUMN OWNER TO RENAME TO SET SCHEMA > > Well, it is a view, not a table. They are basically shorthands for > queries and places to attach triggers, so its normal they do not have > as much management options. > > > If its not a bug and a limitation kindly guide me towards any > documentation > > where it is mentioned. > > Right at the top of create view? : > > >>>> > Description > > CREATE VIEW defines a view of a query. The view is not physically > materialized. Instead, the query is run every time the view is > referenced in a query. > > CREATE OR REPLACE VIEW is similar, but if a view of the same name > already exists, it is replaced. The new query must generate the same > columns that were generated by the existing view query (that is, the > same column names in the same order and with the same data types), but > it may add additional columns to the end of the list. The calculations > giving rise to the output columns may be completely different. > <<<<<< > > 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 > -- Thanks and Regards, Sachin Kotwal
[GENERAL] Migration from DB2 to PostgreSQL
Function in DB2: BLOB()Criteria:Size of character string targeted for cast is more than 1GBHow can I migrate this function into PostgreSQL with above mention criteria. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759607.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Migration from DB2 to PostgreSQL
While migrating my application from DB2 to PostgreSQL. I want to migrate following functions in PostgreSQL. Functions in DB2: BLOB()/CLOB() Criteria: Size of character string targeted for cast is more than 1GB. Character String as argument to this function. How can I migrate this function into PostgreSQL with above mention criteria. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759800.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration from DB2 to PostgreSQL
While migrating my application from DB2 to PostgreSQL. I want to migrate following functions in PostgreSQL. TO_CHAR() in DB2 which can take three arguments as follows: SELECT TO_CHAR(CURRENT_DATE,'-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 I am not sure what is the purpose of third argument in TO_CHAR() function of DB2. I think we can create one user define function with name TO_CHAR which can take three argument like DB2. I want to know that if we want to create user define function TO_CHAR(). What should be the data type of third argument? which can handle maximum data types and more suitable for real time scenarios. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration from DB2 to PostgreSQL-ROLLUP()
While migrating my application from DB2 to PostgreSQL. I want to migrate ROLLUP() function in PostgreSQL. Example: SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ) ORDER BY WEEK, DAY_WEEK This example shows two simple ROLLUP queries followed by a query which treats the two ROLLUPs as grouping sets in a single result set and specifies row ordering for each column involved in the grouping sets. How can I migrate ROLLUP() function in PostgreSQL? or is there any similar function in PostgreSQL which can done this job? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-ROLLUP-tp5759825.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] Migration from DB2 to PostgreSQL
>PostgreSQL has no such capability. Unless you need that and >want to code it yourself, the best solution would be to >write a function that just ignores the third argument. For time being I will write a function that just ignores the third argument. but if we really want to create such function like DB2 TO_CHAR() we need to code it. like setting locale as third argument or format string according to third argument and return it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820p5760265.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] Migration from DB2 to PostgreSQL
>as i know each value is limited to 1GB. For larger content use module lo
>http://www.postgresql.org/docs/9.2/static/lo.html
I just want to know that like DB2 we can convert other data types into
CLOB/BLOB using function CLOB()/BLOB().
Example:
SELECT CLOB('testdata') FROM SYSIBM.SYSDUMMY1
Same way i want to convert other data types into large objects Oid in
PostgreSQL.
Proposed SQL will like:
select col1::oid from hoge;
--OR--
select to_oid('testdata');
Can anyone tell me that how I can implement above function for conversion?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759800p5760319.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)
While migrating my application from DB2 to PostgreSQL.
I want to migrate TIMESTAMP() function of DB2 into PostgreSQL.
Example in DB2:
#SELECT TIMESTAMP('2013-01-01','12:13:14') FROM SYSIBM.SYSDUMMY1
1
--
2013-01-01-12.13.14.00
1 record(s) selected.
==
Example PostgreSQL:
#SELECT TIMESTAMP('2013-01-01','12:13:14');
ERROR: syntax error at or near "'2013-01-01'" at character 18
STATEMENT: SELECT TIMESTAMP('2013-01-01','12:13:14');
ERROR: syntax error at or near "'2013-01-01'"
LINE 1: SELECT TIMESTAMP('2013-01-01','12:13:14');
After executing above SQL I am getting error.
I checked definition of TIMESTAMP().
It is as follows:
test=# \df timestamp
List of functions
Schema | Name| Result data type | Argument
data types | Type
+---+-+--+
pg_catalog | timestamp | timestamp without time zone | abstime
| normal
pg_catalog | timestamp | timestamp without time zone | date
| normal
pg_catalog | timestamp | timestamp without time zone | date, time without
time zone | normal
pg_catalog | timestamp | timestamp without time zone | timestamp without
time zone, integer | normal
pg_catalog | timestamp | timestamp without time zone | timestamp with time
zone | normal
(5 rows)
As we can see TIMESTAMP() hast two arguments date and time without time
stamp but still it is giving error to me.
Please reply if any suggestion.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389.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] Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)
I have done some more try as follows:
#select timestamp(current_date);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date);
==
#SELECT TIMESTAMP(to_date('2013-01-01','-MM-DD'));
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT TIMESTAMP(to_date('2013-01-01','-MM-DD'));
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','-MM-DD'));
==
select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date" at character 18
STATEMENT: select timestamp(current_date,current_time);
ERROR: syntax error at or near "current_date"
LINE 1: select timestamp(current_date,current_time);
==
#SELECT TIMESTAMP(to_date('2013-01-01','-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date" at character 18
STATEMENT: SELECT
TIMESTAMP(to_date('2013-01-01','-MM-DD'),'17:15:43'::time);
ERROR: syntax error at or near "to_date"
LINE 1: SELECT TIMESTAMP(to_date('2013-01-01','-MM-DD'),'17:15:4...
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761390.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] Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)
Using your link
http://www.postgresql.org/docs/9.2/static/functions-formatting.html
In DB2 when I use following command I am getting output combined date and
time i passed to function.
#SELECT TIMESTAMP('2013-01-01','12:13:14') FROM SYSIBM.SYSDUMMY1
1
--
2013-01-01-12.13.14.00
1 record(s) selected.
==
If I execute same command with TO_TIMESTAMP() function I am getting default
date.
SELECT TO_TIMESTAMP('2013-01-01','12:13:14');
to_timestamp
-
0001-01-01 00:00:00+05:53:28 BC
(1 row)
So output is different, so I can not use TO_TIMESTAMP() function as it is.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761626.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] Migration from DB2 to PostgreSQL-TIMESTAMP(arg1,arg1)
>I've done something weird:
>CREATE OR REPLACE FUNCTION "timestamp"(_date date, _time time) RETURNS
timestamp AS $$
>SELECT _date + _time;
>$$ LANGUAGE sql;
>SELECT "timestamp"('2013-01-01'::date, '12:00:00'::time);
Good one.
function with above definition is already present in pg_catalog. so no need
to define new function.(3 rd row)
\df timestamp
List of functions
Schema | Name| Result data type | Argument
data types | Type
+---+-+--+
pg_catalog | timestamp | timestamp without time zone | abstime
| normal
pg_catalog | timestamp | timestamp without time zone | date
| normal
pg_catalog | timestamp | timestamp without time zone | date, time without
time zone | normal
pg_catalog | timestamp | timestamp without time zone | timestamp without
time zone, integer | normal
pg_catalog | timestamp | timestamp without time zone | timestamp with time
zone | normal
(5 rows)
But why this function requires "" to get execute.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-TIMESTAMP-arg1-arg1-tp5761389p5761628.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] Different transaction log for database/schema
>where I need to run simultaneously different tests using transactions Are you running tests simultaneously on different databases? i.e. access to different databases within one transaction. If not -- after each transaction you can do pg_switch_xlog() then new transaction log will be created and next transaction details will store in new log file. make sure that you set proper configuration parameter to keep maximum transactions logs. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure (re-sent, shorter)
Its looks like your previous un-installation was not done properly. Uninstall it properly first then restart your system. check pre-requisite for postgresql if any. then install Postgres 9.2.4. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-9-2-4-for-Windows-Vista-Dell-Vostro-400-re-installation-failure-re-sent-shorter-tp5764212p5765126.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration from Symfoware to PostgreSQL-Constructor functions
Hello While migrating small application from Symfoware to PostgreSQL. There are some constructor functions in Symfoware. Which I unable to execute in Symfoware database. If anyone knows how to execute constructor functions in Symfoware. how to see list of system tables, functions, view and user defined tables, functions, views. Please reply. - Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-Symfoware-to-PostgreSQL-Constructor-functions-tp5766203.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] inserting huge file into bytea cause out of memory
>I got a out of memory problem, when I tried to insert a binary file (256MB) to bytea column; >I want to get a way to insert files (vary from 1byte to 2GB) or byte array or binary stream into >PostgreSQL bytea field, never cause out of memory. Fellowed by the details. >Anybody know about this, please write to me. >Thanks in advance! Maximum data size allowed to store in BYTEA data types is 1GB. so you can store data less than 1 GB. When you are inserting system asking for more data than your expectation. by this message- "Details:Failed on request of size 268443660" so please check anything wrong elsewhere in your code. Also check free memory of your system, at the time of insertion. http://www.microolap.com/products/connectivity/postgresdac/help/TipsAndTricks/ByteaVsOid.htm ----- Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/inserting-huge-file-into-bytea-cause-out-of-memory-tp5766466p5766503.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] filling database
create sample table with one or two rows then use following command to populate data. INSERT INTO TABLE_NAME VALUES(generate_series(1,10)); - Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/GENERAL-filling-database-tp1843856p5768376.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Naming conventions for column names
Hi All, Is there any reason to keep column names as usesysid and senate instead of usersysid and username ? postgres=# select * from pg_stat_replication ; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | stat e | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state --+--+-+--+---+-+-+---+--+--- +---+++-+---+ 2297 |24522 | replica | walreceiver | 192.168.16.53 | | 49782 | 2017-08-15 00:45:43.256404-04 |14938 | stream ing | 111/BD9D5328 | 111/BD9D5328 | 111/BD9D5328 | 111/BD9D52F0| 0 | async (1 row) -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] Naming conventions for column names
Hi All, Correcting my words. Is there any special reason to keep column names as usesysid and usename instead of usersysid and username in below system View? On Mon, Nov 6, 2017 at 4:03 PM, Sachin Kotwal wrote: > Hi All, > > Is there any reason to keep column names as usesysid and senate instead of > usersysid and username ? > > > > postgres=# select * from pg_stat_replication ; > pid | usesysid | usename | application_name | client_addr | > client_hostname | client_port | backend_start | > backend_xmin | stat > e | sent_location | write_location | flush_location | replay_location | > sync_priority | sync_state > --+--+-+--+- > --+-+-+- > --+--+--- > +---+++- > +---+ > 2297 |24522 | replica | walreceiver | 192.168.16.53 | > | 49782 | 2017-08-15 00:45:43.256404-04 |14938 | stream > ing | 111/BD9D5328 | 111/BD9D5328 | 111/BD9D5328 | 111/BD9D52F0| > 0 | async > (1 row) > > > > > -- > > Thanks and Regards, > Sachin Kotwal > -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] Naming conventions for column names
Hi Peter, I can understand that it is important to maintain naming pattern same as system catalogs, but in that case we may need to redefine system catalogs naming conventions . So that we can use those newly added naming conventions in system views as well. It is difficult to understand usename = database user name and usesysid = system user id. It is better to use full names to those columns, so that Users can easily understand those columns by their names. In this case adding one or more laters will not cause any problem but will add more readability. In case adding 3 letters indicating the catalog , can we use 4 or 5 letters ? I think we need to rethink about these short naming conventions which are making confusion, If community is ok with that. Please share yours thoughts on this. Regards, Sachin Kotwal On Mon, Nov 6, 2017 at 6:21 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 11/6/17 05:36, Sachin Kotwal wrote: > > Is there any special reason to keep column names as usesysid > > and usename instead of usersysid and username in below system View? > > The reason to *keep* them is compatibility. The reason they are like > that to start with is because that is the naming pattern used in the > system catalogs: 3 letters indicating the catalog, plus additional > letters or words. It is useful to use the same name in views such as > pg_stat_replication, so you can easily join different views and catalogs. > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] Naming conventions for column names
Hi Tom, You are right. Those naming conventions are old and that is why we have to improve those where ever and when ever required. If no one has objection, I will give a try to improve this part. I believe these naming conventions will be at two levels: 1. Internal code of PostgreSQL , structures getting used internally 2. SQL/C functions get executed at the time of database initialization to create default objects and system catalogs. I will see how much modifications/efforts need to be done and will come back again if it is feasible. My intension is to improve naming conventions and increase naming string where naming conventions are correct but make shorten. Suggestions and feedbacks are welcome. Regards, Sachin Kotwal On Mon, Nov 6, 2017 at 8:03 PM, Tom Lane wrote: > Sachin Kotwal writes: > > I can understand that it is important to maintain naming pattern same as > > system catalogs, but in that case we may need to redefine system catalogs > > naming conventions . > > Those naming conventions are twenty-five years old, and there is an > astonishing amount of client code that would break if we ran around > changing existing system catalog column names. It's very unlikely that > any proposal to do that would even receive serious consideration. > > The bar to using new naming conventions in new catalogs would be > lower, of course, but then you have to think about the confusion > factor of having different naming styles in different places. > > regards, tom lane > -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] Naming conventions for column names
Hi, It seems people worrying about failure of client side code after changes in column names. Melvin also mention that just change in one column was broken many things. > > > My intension is to improve naming conventions and increase naming string > > where naming conventions are correct but make shorten. > > Sure. It make sense. Definitely we can do this. Specially patch reviewers and committers need to take care of this. (I am not ordering to anyone here, just putting my thoughts) > I think the proper amount of effort to rename existing system catalog > columns is zero. > > Can you clarify how efforts will be zero. Is there any script in place or automatic way of doing this? > Also, I think it's pretty difficult to change column names on views that > have already been released. The compatibility break for existing tools > is just too large. > > It is correct. But then I think we can do below things: 1. Instead of pushing all changes at once , we can group them and push them slowly in each major version release. 2. We need to decide weather we really need those changes in old versions? 3. Notify or highlight these changes in release notes because this can break some existing tools and user code. 4. As Alvaro suggested, when developer working on any major code change, He should give proper naming conventions to system catalog/view column names in that area. If It is impossible or not required to do this, then we can stop discussion with 4th point made by Alvaro as conclusion of this discussion. Please committers give their final view on this. -- Thanks and Regards, Sachin Kotwal
Re: [GENERAL] Partitioning table with billion row
1. You have to remove foreign key reference from table searchcache. 2. take backup of data from searchcache. 3. create partition of table product 4. add constraints on table searchcache.(if necessary delete and create searchcache after taking backup.) - Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Partitioning-table-with-billion-row-tp5771582p5772155.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] Problems with vacuum
Please look at following topic:- http://www.postgresql.org/docs/9.0/static/functions-admin.html try using #SELECT pg_terminate_backend(2738); - Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problems-with-vacuum-tp5769548p5772158.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] pg_similarity
I tried the installation as suggested at http://pgsimilarity.projects.pgfoundry.org/ after make install command we have run following command:- psql -f SHAREDIR/contrib/pg_similarity.sql mydb Here SHAREDIR is /usr/local/pgsql/share/extension/ under this directory we can see file pg_similarity.sql and mydb is your database name. so provide correct path and database then pg_similarity will create it's classes,functions and operators etc. after that you can load pg_similarity using SQL command load 'pg_similarity'; or copy a sample file at tarball (pg_similarity.conf.sample) to PGDATA (as pg_similarity.conf) and include the following line in postgresql.conf:- include 'pg_similarity.conf' ----- Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-similarity-tp5774125p5774835.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] C-language stored function and float4 type
How you checked result type? Can you explain in details? - Thanks and Regards, Sachin Kotwal NTT-DATA-OSS Center (Pune) -- View this message in context: http://postgresql.1045698.n5.nabble.com/C-language-stored-function-and-float4-type-tp5773493p5774840.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
