[GENERAL] how to replace last 4 digital phone number into star using regexp_replace?
hi, friends,
Our phone number format is 11 digital, i want to replace last 4 digital
into star, for example:
Replace this string including 11 digital phone number:
'1891018,1391018,232,abc'
into:
'1891018,1391018,232,abc'
I try to use:
select
regexp_replace('1891018,1391018,232,abc','[0-9]{7}(\d+)','','g')
But it doesn't work, it will return:
",,232,abc"
Can anyone help me? Thanks a million!
Best Regards
peng
[GENERAL] Does PostgreSQL support BIM(Building Information Modeling) storage？
Does PostgreSQL support BIM(Building Information Modeling) storage？Or how? I can only find few infomation: http://repository.tudelft.nl/islandora/object/uuid:dcb7fc18-208c-4e3b-bc2a-d24a2346d44b?collection=research Are there any other articles/books/software recommended? Thanks！ peng
[GENERAL] are there any method that "Update" command not affect other unrelated indices?
Hi, I have the following table:
CREATE TABLE A
(
a1 integer not null,
a2 integer,
a3 integer,
a4 integer
)
and have the following four indices:
create index ind_a1 on A USING gist(a1);
create index ind_a2 on A USING gist(a2);
create index ind_a3 on A USING gist(a3);
create index ind_a4 on A USING gist(a4);
now we have 10,000 update command executions using spi_exeplan():
SPI_prepare(); // prepare the plan for "update A set a4 = $1;"
for(i=0;i<1;i++ ){
SPI_execute_plan();// update A set a4 = i;
}
the question is why all four indices updated in the execution of
SPI_execute_plan()?
I think there should only one index, that is ind_a4 be updated, how to avoid
other three indices updated?
thanks!
Re: [GENERAL] are there any method that "Update" command not affect other unrelated indices?
Thanks. I could give more clues. The call stack of the function most consumed time is: Thread [1] (Suspended) 34 ExecInsertIndexTuples() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execUtils.c:1046 0x08201e66 33 ExecUpdate() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:2135 0x081f3b13 32 ExecutePlan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:1681 0x081f31c6 31 standard_ExecutorRun() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:309 0x081f0f4b 30 ExecutorRun() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:258 0x081f0e04 29 _SPI_pquery() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:2009 0x0821fe8c 28 _SPI_execute_plan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:1831 0x0821facd 27 SPI_execute_plan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:392 0x0821d201 in execMain.c, the call of ExecInsertIndexTuples() is as following: if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple)) ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); 2010/10/12 Ben Carbery > Well, the objects indices 1,2,3 point to changed when you changed column > a4, but I don't know if that's the reason. I would guess that the indices > are structured as pointers of some kind though. > > > On Wed, Oct 13, 2010 at 9:03 AM, sunpeng wrote: > >> >> the question is why all four indices updated in the execution of >> SPI_execute_plan()? >> I think there should only one index, that is ind_a4 be updated, how to >> avoid other three indices updated? >> thanks! >> >>
[GENERAL] how to get current sql execution time?
when I use the psql to send a sql, how to get current sql execution time?
[GENERAL] how to write an optimized sql with two same subsql?
We have a table A: CREATE TABLE A( uid integer, groupid integer ) Now we use this subsql to get each group's count: SELECT count(*) as count FROM A GROUP BY groupid ORDER BY groupid Then we try to find the group pair with following conditions: SELECT c.groupid as groupid1,d.groupid as groupid2 FROM subsql as c, subsql as d WHERE d.groupid > c.groupid and d.count > c.count; Does that mean subsql will be executed twice? or how to write the optimized sql?
Re: [GENERAL] how to write an optimized sql with two same subsql?
Actually I've simplied my original sql to the previous version, since it's simple yet reveals the same problem. My original sql is to get two instersected cluster(as same concept as group ) and its commonarea: SELECT a.clusterid AS clusterida, b.clusterid AS clusteridb, *St_astext*(*St_intersection*(a.bufferbox, b.bufferbox)) AS commonarea FROM (SELECT *St_buffer*(*St_convexhull*(*St_collect*(c.a0)), 2100.00) AS bufferbox, d.clusterid AS clusterid FROM _mcir_2347694 c, _mcir_2347694_clusterid2 d WHERE c.uid = d.uid GROUP BY d.clusterid) a, (SELECT *St_buffer*(*St_convexhull*(*St_collect*(c.a0)), 2100.00) AS bufferbox, d.clusterid AS clusterid FROM _mcir_2347694 c, _mcir_2347694_clusterid2 d WHERE c.uid = d.uid GROUP BY d.clusterid) b WHERE b.clusterid > a.clusterid AND *St_intersects*(a.bufferbox, b.bufferbox) ORDER BY a.clusterid; The DDL for _mcir_2347694 and _mcir_2347694_clusterid2 is: CREATE TABLE _mcir_2347579 ( a0 geometry, uid integer ) CREATE TABLE _mcir_2347579_clusterid2 ( uid integer NOT NULL, clusterid integer ) In these two tables, _mcir_2347579_clusterid2.uid = mcir_2347579.uid, just like a forign key. The same question is how to avoid the following subquery be executed twice: SELECT *St_buffer*(*St_convexhull*(*St_collect*(c.a0)), 2100.00) AS bufferbox, d.clusterid AS clusterid FROM _mcir_2347694 c, _mcir_2347694_clusterid2 d WHERE c.uid = d.uid GROUP BY d.clusterid 2010/10/14 Rob Sargent > > > On 10/14/2010 05:34 PM, sunpeng wrote: > > We have a table A: > > CREATE TABLE A( > >uid integer, > >groupid integer > > ) > > Now we use this subsql to get each group's count: > > SELECT count(*) as count > > FROM A > > GROUP BY groupid > > ORDER BY groupid > > > > Then we try to find the group pair with following conditions: > > SELECT c.groupid as groupid1,d.groupid as groupid2 > > FROM subsql as c, subsql as d > > WHERE d.groupid > c.groupid > > and d.count > c.count; > > > > Does that mean subsql will be executed twice? or how to write the > > optimized sql? > > > > > What are you trying to discover about groups and their frequency in > tablea? Does the numberical value of groupid have any meaning in your > system? > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] how to get the height of index tree?
Hi, how to get the height of R* or B tree of created index ?
Re: [GENERAL] how to get the height of index tree?
what's the base of log*a*(*n*) function ? is that base a=16? or something else? how to know this base ? 2010/10/22 sunpeng > Hi, how to get the height of R* or B tree of created index ? >
[GENERAL] why update is slower on my pc?
I use my laptop to execute the following sql, it's: mydb=# update _mcir_2597431_clusterid2 set clusterid = 3; UPDATE 104770 Time: 8666.447 ms and on my pc: mydb=# update _mcir_2597431_clusterid2 set clusterid = 3; UPDATE 104770 Time: 27171.203 ms First I wondered whether the write speed on pc is lower than laptop, so i use a cp command to test a write speed: on my laptop: postg...@postgres-laptop:~$ time cp backup/soft/data\ mining/SPSS\ Clementine\ v11.rar develop/ real0m19.403s user0m0.032s sys0m3.472s on my pc: postg...@postgres-laptop:~$ time cp backup/soft/data\ mining/SPSS\ Clementine\ v11.rar develop/ real0m9.192s user0m0.008s sys0m1.708s so the writing speed on disk of pc is much faster than laptop, why the update sql command is much slower than my laptop? what's the reason causing such decrease?
[GENERAL] How to store fixed size images?
We have many small size(most fixed size) images, how to store them? There are two options: 1. Store images in folders, managed by os file system, only store path in postgresql 2. Store image as bytea in postgresql How do you usually store images? Thanks! peng
Re: [GENERAL] How to store fixed size images?
Thank you, Jeff! peng On Wed, Jun 18, 2014 at 12:15 AM, Jeff Janes wrote: > On Mon, Jun 16, 2014 at 6:10 PM, sunpeng wrote: > > We have many small size(most fixed size) images, how to store them? There > > are two options: > > 1. Store images in folders, managed by os file system, only store path in > > postgresql > > 2. Store image as bytea in postgresql > > How do you usually store images? > > I use method 1, because the library/modules I made use of only > implemented that method. I'd prefer to use method 2, but not enough > to write the code for doing it when there was existing code. The > problem with 1 is now you have two streams of data to back up, and the > data itself is no longer transactional with its metadata. A potential > problem with 2 is that it will run into problems if any of the data is > more than a small fraction of RAM. So the images must be "always > small". If they are just "usually small", that isn't good enough. > Another problem with bytea is the encoding issues. Good up-to-date > drivers will handle that for you (mostly) transparently, but there are > lots of drivers that are not good, or not up-to-date. > > Cheers, > > Jeff >
[GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
When I do migration from Mysql to PostgreSQL: firstly dump data from mysql in cmd(encoding is GBK) is WIN8: mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names -uroot -p test >dbdata.sql then load data to postgresql in cmd(encoding is GBK) is WIN8: psql -h localhost -d test -U postgres < dbdata.sql I got the error: ERROR: invalid byte sequence for encoding "UTF8": 0xff I checked in dbdata.sql using UltraEdit, data "0xff" really exists as followings: 蛾4?4抿\0xfF???±??x¤? and I use UltraEdit menu-> view -> encoding page to get: 936 GBK. DDL in Mysql 5.5 is: CREATE TABLE `personpicture` ( `ID` char(32) NOT NULL, `Picture` mediumblob, ... KEY `personId` (`PersonID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DDL in PostgreSQL 9.1 is: create database test encoding 'utf8'; CREATE TABLE personpicture ( ID char(32) NOT NULL, Picture BYTEA, PRIMARY KEY (ID) ); The error is related to Picture mediumblob and BYTEA, which is stored pic binary data. My Operationg system is WIN8 64bit. I've set postgresql.conf before starting everything, but error still occurs: backslash_quote = on escape_string_warning = off standard_conforming_strings = off How to resolve it? Thanks! peng
Re: [GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
I‘v changed cmd (in win8) to encoding utf8 through chcp 65001, but error still occurs. And i use the following cmd to dump mysql data: mysql> select Picture from personpicture where id = 'F2931306D1EE44ca82394CD3BC2404D4' into outfile "d:\\1.txt" ; I got the ansi file, and use Ultraedit to see first 16 bytes: FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 5C It's different from mysql workbench to see: FF D8 FF E0 00 10 4a 46 49 46 00 01 01 00 00 01 peng On Tue, Jul 1, 2014 at 9:18 PM, Kevin Grittner wrote: > sunpeng wrote: > > > load data to postgresql in cmd(encoding is GBK) is WIN8: > > > psql -h localhost -d test -U postgres < dbdata.sql > > > > I got the error: > > ERROR: invalid byte sequence for encoding "UTF8": 0xff > > If the encoding is GBK then you will get errors (or incorrect > characters) if it is read as UTF8. Try setting the environment > variable PGCLIENTENCODING. > > http://www.postgresql.org/docs/9.1/static/app-psql.html > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
[GENERAL] How to use ADO to insert BYTEA data?
I try to write this code for postgresql (for mysql working fine):
try
{
m_pRecordset->AddNew();
iTimes++;
VARIANT bitdata[3];
SAFEARRAY *psafe[3] = {NULL, NULL, NULL};
SAFEARRAYBOUND band[3];
bitdata[0].vt = VT_ARRAY | VT_UI1;
band[0].cElements = FreImgSize;
band[0].lLbound = 0;
psafe[0] = SafeArrayCreate(VT_UI1, 1, &band[0]);
for (long iSize = 0; iSize < FreImgSize; iSize++)
{
SafeArrayPutElement(psafe[0], &iSize,
&pFreImg[iSize]);
}
bitdata[0].parray = psafe[0];
if (bitdata[0].parray != NULL)
{
m_pRecordset->GetFields()->GetItem("CapturePic")->AppendChunk(&bitdata[0]);
}
When calling AppendChunk error occurs.
Please help me, thks!
peng
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?
my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc version (32 or 64 bit) should be installed in Client ? is psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? thks! peng -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?
my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc version (32 or 64 bit) should be installed in Client ? is psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? thks! peng
Re: [GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?
when using psqlodbc_09_01_0200.zip, when i store picture to bytea using vc2008, calling appendchunk will get com_error. On 7/4/14, sunpeng wrote: > my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc > version (32 or 64 bit) should be installed in Client ? is > psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? > thks! > peng > -- 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] which odbc version (32 or 64 bit) should be installed in Client ?
Why do I get 800A0C93 errors? If you are getting one of these errors: ADODB.Recordset error '800a0c93' Operation is not allowed in this context. or I got ADODB.Recordset error '800a0c93' The operation requested by the application is not allowed in this context. or ADODB.Recordset error '800a0c93' Illegal Operation. If you are using an ADODB.Recordset to UPDATE / INSERT (e.g. using the AddNew method), use a direct SQL statement instead (see Article #2191). see http://tutorials.aspfaq.com/8000x-errors/why-do-i-get-800a0c93-errors.html how to do? peng On 7/4/14, sunpeng wrote: > when using psqlodbc_09_01_0200.zip, > when i store picture to bytea using vc2008, > calling appendchunk will get com_error. > > On 7/4/14, sunpeng wrote: >> my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc >> version (32 or 64 bit) should be installed in Client ? is >> psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? >> thks! >> peng >> > -- 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] which odbc version (32 or 64 bit) should be installed in Client ?
Thank you, all friends, I will post "appendchunk error hresult 800a0c93" to pg_odbc mailing list. peng On 7/4/14, John R Pierce wrote: > On 7/3/2014 5:08 PM, sunpeng wrote: >> my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which >> odbc version (32 or 64 bit) should be installed in Client ? is >> psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? > > a 32 bit client OS would *mandate* a 32bit ODBC.even on a 64bit > client OS, if you have 32 bit applications, they also require 32 bit > ODBC drivers. only if both the OS *and* the client applications are > 64bit would a 64bit ODBC be suitable. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- 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 error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
Thank you, friend, I use --hex-blob : mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names --hex-blob -uroot -p test videorecresult >dbdata.sql to dump mysql data. And replace blob data "0x" into "E'\\xx'" to load data into postgresql. On Fri, Jul 4, 2014 at 3:27 PM, Albe Laurenz wrote: > sunpeng wrote: > >>> load data to postgresql in cmd(encoding is GBK) is WIN8: > >>> > >>> psql -h localhost -d test -U postgres < dbdata.sql > >>> > >>> I got the error: > >>> ERROR: invalid byte sequence for encoding "UTF8": 0xff > > >> If the encoding is GBK then you will get errors (or incorrect > >> characters) if it is read as UTF8. Try setting the environment > >> variable PGCLIENTENCODING. > >> > >> http://www.postgresql.org/docs/9.1/static/app-psql.html > > > I‘v changed cmd (in win8) to encoding utf8 through chcp 65001, but error > still occurs. > > And i use the following cmd to dump mysql data: > > mysql> select Picture from personpicture where id = > 'F2931306D1EE44ca82394CD3BC2404D4' into outfile > > "d:\\1.txt" ; > > I got the ansi file, and use Ultraedit to see first 16 bytes: > > FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 5C > > > > It's different from mysql workbench to see: > > FF D8 FF E0 00 10 4a 46 49 46 00 01 01 00 00 01 > > Changing the terminal code page won't do anything, it's probably the data > that are in a different encoding. > > I don't know enough about MySQL to know which encoding it uses when > dumping data, > but the man page of "mysqldump" tells me: > > --set-charset > Add SET NAMES default_character_set to the output. This option is > enabled by default. > > So is there a SET NAMES command in the dump? If yes, what is the argument? > > You will have to tell PostgreSQL the encoding of the data. > As Kevin pointed out, you can do that by setting the environment variable > PGCLIENT ENCODING to the correct value. Then PostgreSQL will convert the > data automatically. > > Yours, > Laurenz Albe >
[GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?
Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL? Thanks! peng
Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!
Cool, I hope I could be a member of patch reviewers. peng sun On Thu, Jun 16, 2011 at 4:58 AM, Merlin Moncure wrote: > On Wed, Jun 15, 2011 at 3:49 PM, Josh Berkus wrote: > > PostgreSQL community members: > > > > Do you love the PostgreSQL project? Do you want to contribute to it? > > Do you want to help produce the next version of PostgreSQL? (9.2) > > > > Well, you *can*. You can be a patch reviewer -- one of the single most > > valuable things you can contribute to the project. > > > > You do not have to be a C coder to be a patch reviewer. Pretty much all > > you need to know is: > > - how to checkout PostgreSQL from Git > > - how to build PostgreSQL from source > > - how to apply a patch > > > > If you know those three things, you can help with patch review. Of > > course, if you do know C, you can be even more help ... and learn the > > PostgreSQL source in the process. > > > > We especially need folks who are able to build PostgreSQL on Windows, as > > we have several Windows-specific patches and no reviewers for them. > > > > The First CommitFest for version 9.2 is underway *right now* and we need > > your help. Help PostgreSQL ... review a patch! > > > > http://wiki.postgresql.org/wiki/Reviewing_a_Patch > > This sounds suspiciously like you are trying to get me to join the army... > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] hi, is that the standard:ISO/IEC 13249-6:2006 if I investigate the data mining extension for SQL language?
Recently I noticed there are only 1-4, 9-14 parts in SQL:2008 standard: ISO/IEC 9075-1:2008. Here is: http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=45498 why don't they define 5-8 parts in this standard? I also noticed there is a standard for data mining: ISO/IEC 13249-6:2006, here is: http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=38648 . is that the standard if I investigate the data mining extension for SQL language? Thanks!
[GENERAL] Hi,Frirends, are there any commands in pgsql/bin/ corresponding "create tablespace"?
Hi,Frirends, Are there any commands in pgsql/bin/ corresponding "create tablespace"? I know: createuser <==> create role createdb <==>create database Thanks! peng
[GENERAL] where are the getting buf data from disk really done?
I noticed at the function StartBufferIO() in bufmgr.c, there is no really getting buffer data from disk, only set InProgressBuf = buf; and return true; i wondered where is the getting buf data from disk really done? are there a background process to do this work? which function does this work? thanks! peng
[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?
how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? if i use eclipse cdt to attach the postgres process forked by postmaster, this part of codes from the begining of this PostgresMain() to the "for (;;)" loop could not be debugged. So anybody know how to debug this part codes? thanks. thanks peng
[GENERAL] how to set CACHEDEBUG ?
I noticed there is a piece of code:
#ifdef CACHEDEBUG
#define InitCatCache_DEBUG2 \
do { \
elog(...
} while(0)
#else
#define InitCatCache_DEBUG2
#endif
Now I'd like to set CACHEDEBUG, how to set up it ? where ?
thanks
peng
[GENERAL] only one namespace allowed by a authid at pg_namespace table?
only one namespace allowed by a authid at pg_namespace table? for in the
function:
static void recomputeNamespacePath(void){
...
if (strcmp(curname, "$user") == 0)
{
/* $user --- substitute namespace matching user name, if any */
HeapTupletuple;
tuple = SearchSysCache(AUTHOID,
ObjectIdGetDatum(roleid),
0, 0, 0);
if (HeapTupleIsValid(tuple))
{
char *rname;
rname = NameStr(((Form_pg_authid)
GETSTRUCT(tuple))->rolname);
namespaceId = GetSysCacheOid(NAMESPACENAME,
CStringGetDatum(rname),
0, 0, 0);
ReleaseSysCache(tuple);
if (OidIsValid(namespaceId) &&
!list_member_oid(oidlist, namespaceId) &&
pg_namespace_aclcheck(namespaceId, roleid,
ACL_USAGE) == ACLCHECK_OK)
oidlist = lappend_oid(oidlist, namespaceId);
}
}
}
...
}
I noticed with the revoke of function GetSysCacheOid(NAMESPACENAME,
CStringGetDatum(rname),0, 0, 0); only return one oid of namespace.
[GENERAL] why there are two TargetEntrys in Query when i use "select count(catcode) from pois group by catcode"?
I've the table
CREATE TABLE pois
(
uid integer not null,
name VARCHAR(128),
catcode VARCHAR(32) not null,
catname VARCHAR(32),
others VARCHAR(32)
);
after i execute "select count(*) from pois group by catcode";
the log of query.targetList is :
:targetList (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:args <>
:agglevelsup 0
:aggstar true
:aggdistinct false
:location 7
}
:resno 1
:resname count
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 1043
:vartypmod 36
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 35
}
:resno 2
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk true
}
)
why there is the second TARGETENTRY? what's the purpose of the second
TARGETENTRY?
[GENERAL] when to update pg_statistic relation?
when to update pg_statistic relation? is it when inserting a new tuple of any user's relations? and the relation pg_stats at document 8.4 chapter 44.55.pg_stats hasn't been used anymore ? another question is: I noticed when i send the sql :"select catcode from pois goup by catcode", the function :double estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows) uses pg_statistic.staattnum to set Agg.numGroups, then if the pg_statistic.staattnum is incorrect ,does postgresql still use this infomation to set Agg.numGroups ?
[GENERAL] what's the relation between pathkey and path in optimize phase?
what's the relation between pathkey and path in optimize phase? thanks peng
[GENERAL] when to update pg_statistic relation?
when to update pg_statistic relation? is it when inserting a new tuple of any user's relations? and the relation pg_stats at document 8.4 chapter 44.55.pg_stats hasn't been used anymore ? another question is: I noticed when i send the sql :"select catcode from pois goup by catcode", the function :double estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows) uses pg_statistic.staattnum to set Agg.numGroups, then if the pg_statistic.staattnum is incorrect ,does postgresql still use this infomation to set Agg.numGroups ?
[GENERAL] hi,is it dangerous to only use tuple pointer through heap_getnext()?
hi,i use these codes to store only pointer of tuple :
HeapTuple *tuple;
tuple = heap_getnext(pHeapScanDesc,ForwardScanDirection);
while(tuple){
//[1#]here i only store the pointer of tuple in an array for later
using,that means i don't retrive attribute data from this tuple ,is this ok?
myArray[i++]=tuple;
//then next tuple
tuple = heap_getnext(pHeapScanDesc,ForwardScanDirection);
}
at place [1#],i only store the pointer of current tuple in an array for
later using,that means i don't retrieve attribute data from this tuple ,is
this ok?
could these tuple pointers be invalid sometimes later if myArray still
reference these tuples? i know these tuples have been read into buffers in
shared memory, yet I am not sure wether these buffers be replaced or marked
invalid when myArray still points to these tuples in according buffers.
thanks a lot!
peng
[GENERAL] how to get Point * from datum =heap_getattr(....)?
hi,i have this table:
CREATE TABLE pois(
uid integer not null,
name VARCHAR(128),
catcode VARCHAR(32) not null,
catname VARCHAR(32),
others VARCHAR(32)
);
SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2);
and then in my codes,i use this invoke to get location column in tuple:
tuple = heap_getnext(pHeapScanDesc,ForwardScanDirection);
datum = heap_getattr(tuple,6,pRawRelation->rd_att,&isnull);
then on the next,how to write to get Point? is this right?
Point *p = isnull?NULL:(Point *)(PG_DETOAST_DATUM(datum));
i've tried it,but it seems not work.
thanks a lot.
peng
[GENERAL] how to get Point * from datum =heap_getattr(....)?
hi,i have this table:
CREATE TABLE pois(
uid integer not null,
name VARCHAR(128),
catcode VARCHAR(32) not null,
catname VARCHAR(32),
others VARCHAR(32)
);
SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2);
and then in my codes,i use this invoke to get location column in tuple:
tuple = heap_getnext(pHeapScanDesc,
ForwardScanDirection);
datum = heap_getattr(tuple,6,pRawRelation->rd_att,&isnull);
then on the next,how to write to get Point? is this right?
Point *p = isnull?NULL:(Point *)(PG_DETOAST_DATUM(datum));
i've tried it,but it seems not work.
thanks a lot.
peng
[GENERAL] which function should i invoke to create a table and insert tuples?
hi,when i do experiment on postgresql 8.4,i need to create a table and insert some tuples,which function should i invoke? for example,i want to create a table with "create table test (uid int,catcode int)" and insert tuples with "insert into test values(1,1)". thanks millions! peng
Re: [GENERAL] which function should i invoke to create a table and insert tuples?
it's in source codes,actually i'm writting codes in postgresql source codes,just to verify some of my ideas. C language is used. 2010/5/17 Guy Rouillier > On 5/17/2010 10:31 AM, sunpeng wrote: > >> hi,when i do experiment on postgresql 8.4,i need to create a table and >> insert some tuples,which function should i invoke? >> for example,i want to create a table with "create table test (uid >> int,catcode int)" and insert tuples with "insert into test values(1,1)". >> thanks millions! >> > > What do you mean by function? Are you trying to do this from PgAdmin, from > a stored proc or from some flavor of source code? If either of the latter > two, which language? > > -- > Guy Rouillier > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] hi,i write a function in postgresql source code, how to register this function?
hi,i write a function in postgresql source code, how to register this function? it is not an aggregate function. i don't use 34.3"User-Defined Functions" described in http://www.postgresql.org/docs/8.4/interactive/xfunc.html, i just write it in postgresql sourcecode, how to register this function to let final user use? Should i only add one tuple in pg_process table? how to add ? thanks! peng
Re: [GENERAL] hi,i write a function in postgresql source code, how to register this function?
It's just in postgresql 8.4 source code,e.g in /backend/executor/functions.c, not in sql,not in pl/pgsql 2010/6/16 Raymond O'Donnell > On 16/06/2010 17:42, sunpeng wrote: > > hi,i write a function in postgresql source code, how to register this > > function? > > it is not an aggregate function. > > i don't use 34.3"User-Defined Functions" described in > > http://www.postgresql.org/docs/8.4/interactive/xfunc.html, i just write > > it in postgresql sourcecode, how to register this function to let final > > user use? Should i only add one tuple in pg_process table? how to add ? > > What do you mean by "PostgreSQL source code"? - SQL? pl/pgsql? > > Generally, you just execute the following SQL command: > >create or replace function my_function(.) returns [return type] >as >$$ > [function code here] >$$ >language [whatever - usually sql or plpgsql] ; > > > or am I missing something in your question? > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
[GENERAL] are there any documents describe the index mechanic?
are there any documents describe the index mechanic? For example, how to store the B tree in tables in hard disk? thanks! peng
[GENERAL] How to restore a Plan from a stored plan text?
I've used the following codes to translate the PlannedStmt node to a char string: PlannedStmt * pltl = (PlannedStmt *) linitial(plantree_list); Plan *pl = pltl->planTree; char *s; s = nodeToString(pl); How to restore from this s to Plan? I noticed using func parseNodeString() in /backends/nodes/readfuncs.c can't work, for example there is no codes translating into Agg node, should I write my code to parse this string back into PlannedStmt node? Thanks!
Re: [GENERAL] How to restore a Plan from a stored plan text?
Thanks for your help!The motivation is that I try to find the most used sub
plan ,and cach the sub plan's execution result and store sub plan itself on
disk. Even the sub plan's connection is closed, the consequent connection
with the same sub plan could utilize the stored cached result.
For example.
The first connection comes and according history information we find the
most used sub plan, and after execution, i serialize this sub plan node into
a text file and stored the sub plan's execution result on disk:
Plan *subPlan1;
char *s;
s = nodeToString(subPlan1);
//then store s into a text file subPlan1.txt on disk.
//and store the sub plan's execution result
Then the first connection closed.
Now the second connection comes, if the server generate the same sub plan i
could just read the first sub plan's result:
Plan *subPlan2;
char *s ;//then read s from the text file subPlan1.txt on disk
Plan *subPlan1 = deserialized(s);
bool equ = equal(plan1,plan2); //which also can't work for Plan node
if(equ){
//then return the cached first connection's result;
}
...
Then should I write deserialized(s) codes and another equal(void *, void*)
function to support Plan node?
2010/9/3 Tom Lane
> sunpeng writes:
> > I've used the following codes to translate the PlannedStmt node to a char
> > string:
> > PlannedStmt * pltl = (PlannedStmt *) linitial(plantree_list);
> > Plan *pl = pltl->planTree;
> > char *s;
> > s = nodeToString(pl);
>
> > How to restore from this s to Plan?
>
> You can't. The fact that there's nodeToString support for all Plan node
> types is only intended as a debugging aid --- there's no intention that
> it should be possible to serialize and deserialize plans this way.
>
> You didn't say what it is you actually hope to accomplish, but maybe
> asking plancache.c to store the plan for you would do.
>
>regards, tom lane
>
[GENERAL] How to let the created table visible to current process when using SPI_execute("create table ...")?
When Postmaster starts, I've forked another process AP just as syslogger, bgwritter,... In the process AP, If I can't find a table, I would create one, the codes are: char * sqlCreate_DM_ = "create table DM_( ...); "; SPI_connect(); int ret = SPI_execute(sqlCreate_DM_, false, 1); SPI_finish(); but after SPI_finish(); I found the created table DM_ is not visible to current process, how to resolve it? Thanks!
[GENERAL] Fwd: how to write the sql if i want to find a geometry's d-distance neighbors?
I can't mail it to: postgis-us...@postgis.refractions.net so could administrator forword this mail to them? thanks! From: sunpeng Date: 2010/9/12 Subject: how to write the sql if i want to find a geometry's d-distance neighbors? To: postgis-us...@postgis.refractions.net Hi, I've the following table,and has created the index on uid and location; CREATE TABLE points ( location geometry, //assume it's a 2-dimension point uid integer ) Now, I've a point P with uid= 12345, and want to find the neighbors which are within 20, how to write the sql?Is this right? select st_astext(location) from points a, points b where b.rid = 12345 and ST_DWithin(a.the_geom, b.the_geom, 20) or use st_buffer? which one is more efficient ? Thanks!
[GENERAL] hi, how to let the inserted tuple visible to other backend when current backend hasn't finish?
hi, These codes are in the postgresql engine, just assume they are in
PortalRun() function:
//1.create table structure
char *relname = "test";
...
relOid = heap_create_with_catalog(relname, );
CommandCounterIncrement();
...
//2.then i can use SPI_execute to create index on this created table
SPI_connect();
char *sqlCreate_index ="create index on test."
int ret = SPI_execute(sqlCreate_index , false, 1);
SPI_finish();
.
//3.until now it performs well,but after i insert a tuple in this table
Relation mcir_relation = relation_open(relOid); //the relation just created
HeapTupleData htup;
simple_heap_insert(relation, &htup);
CommandCounterIncrement();
...
//4.then again want to invoke SPI_execute("select"), it seems the
inserted tuple is not visible to SPI_execute()
SPI_connect();
int ret = SPI_execute("select * from test;", true, 1);
if (ret == SPI_OK_SELECT && SPI_processed == 1 ) {
}
the ret is SPI_OK_SELECT ,but SPI_processed == 0, the inserted tuple is not
visible to SPI_execute() .
i've used these methods to try to let it visible to SPI_execute() :
simple_heap_insert()
CommandCounterIncrement();
or:
BeginInternalSubTransaction(NULL);
simple_heap_insert()...
ReleaseCurrentSubTransaction();
but they all don't work, how to resolve it?
thanks!
[GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);
First I use SPI_execute("update
bool succ;
SPI_connect();
int ret = SPI_execute("update where uid = 1", false, 1);//later will
add error processing
if (ret == SPI_OK_UPDATE && SPI_processed == 1) {
succ = true;
}
SPI_finish();
Then I use SPI_execute("select .where uid = 1",
SPI_connect();
int ret = SPI_execute("select .where uid = 1", true, 1);//later will
add error processing
if (ret == SPI_OK_SELECT && SPI_processed == 1) {
HeapTuple tuple;
tuple = SPI_tuptable->vals[0];
...
datum = heap_getattr(tuple,attrno,SPI_tuptable->tupdesc,&isnull);
}
the datum doesn't change, why ?
Re: [GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);
I then added , yet it still doesn't work.
2010/9/12 Tom Lane
> sunpeng writes:
> > First I use SPI_execute("update
> > bool succ;
> >SPI_connect();
> > int ret = SPI_execute("update where uid = 1", false, 1);//later
> will
> > add error processing
> > if (ret == SPI_OK_UPDATE && SPI_processed == 1) {
> > succ = true;
> >}
> >SPI_finish();
>
> > Then I use SPI_execute("select .where uid = 1",
>
> > SPI_connect();
> > int ret = SPI_execute("select .where uid = 1", true, 1);//later
> will
> > add error processing
> > if (ret == SPI_OK_SELECT && SPI_processed == 1) {
> >HeapTuple tuple;
> >tuple = SPI_tuptable->vals[0];
> > ...
> >datum =
> heap_getattr(tuple,attrno,SPI_tuptable->tupdesc,&isnull);
> >
> > }
> > the datum doesn't change, why ?
>
> You're missing a CommandCounterIncrement() call between the two actions.
> If you hadn't passed read_only = true to the second SPI_execute, it
> would have done one for you.
>
>regards, tom lane
>
[GENERAL] Are there any commands to see the created index info?
Are there any commands to see the created index info? For example, if I have a table A (has 100,000 tuples )with index A_INDEX, how to see the A_INDEX info, such as the deep of B+ tree? peng
[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?
how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? if i use eclipse cdt to attach the postgres process forked by postmaster, this part of codes from the begining of this PostgresMain() to the "for (;;)" loop could not be debugged. So anybody know how to debug this part codes? thanks. thanks
[GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
hi,Kenichiro,
Thanks for your answer!
Then another question comes: According to 8.4 document 44.1(Most system
catalogs are copied from the template database during database creation and
are thereafter database-specific.),we know that each created database has
their own pg_... tables and thus if a superuser administrator wants to list
all the tables in all the databases,how do the postgresql interval implement
it? will the postgresql interval load all the pg_ tables in all the
databases to get the final answer?
Thanks!
peng
2010/4/8 Kenichiro Tanaka
> Hello sunpeng
>
> First,I answer this question.
>
> >another question:how postgresql internal knows which
> >relations belongs to which database?
>
> When we use "psql" command ,it means "psql -d postgres".
> #you can change default parameter to set PGDATABASE (OS parameter)
>
> "-d" indicate your connecting database and this is the ANSWER.
> So, it is natural that pg_class does not have "database" field.
> We can not access other database with psql without using dblink.
>
> I think we can display some database's pg_class with following command,
>
> #DISPLAY "test" and "postgres"'s tables
>
> #Your postgresql have to be installed dblink
> #1)create dblink
> select dblink_connect('test','host=postgres01 port=1843 dbname=test
> user=p843');
> select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres
> user=p843');
>
> #2) display
> select 'test',* from dblink('test','select oid, relname from pg_class')
> t1(oid oid, relname text)
> union
> select 'postgres',* from dblink('postgres','select oid, relname from
> pg_class') t1(oid oid, relname text);
>
>
>
> Thank you
>
>
> once i have created mydb and several relations in it,are there any sql
>> commands used to list all the tables in this mydb?
>> i noticed there are no database( pg_database.oid) field in pg_class
>> table,so i can not use
>> select relname from pg_class,pg_database where pg_database.datname like
>> 'mydb' and pg_class.database = pg_database.oid;
>> anybody knows how to do it?
>> another question:how postgresql internal knows which relations belongs to
>> which database?
>>
>> thanks
>>
>>
>
> --
>
> Kenichiro Tanaka
> K.K.Ashisuto
> http://www.ashisuto.co.jp/english/index.html
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] hi, friends. are there any performance tuning materials for postgreSQL recommended?
Hi, friends. Are there any performance tuning resouces for postgreSQL recommended, such as ppt, books or articles? Thanks! peng
[GENERAL] hi, firends, what's the difference between using PostgreSQL's share buffer and os's file buffer?
hi, firends, I have a 64bit 128GB machine, I have two choices: 1. I could set PostgreSQL share_buffer to a large value, such as 100GB, let os uses the remaining 28G memory for file system buffer 2. I also could set PostgreSQL share_buffer to a small value, such as 10GB, let os uses the remaining 118G memory for file system buffer what's the difference between this two different methods in PostgreSQL? Which one is prefered? Thanks! peng
[GENERAL] are there any methods to disable updating index before inserting large number tuples?
hi, friends, Now each hour I want to insert about 20 millions tuples into table A, which has a btree index. How to disable index update before inserting those 20 millions tuples, and then enable it after those tuples having being inserted? Just as the followings: >disable index update >insert into A //here will insert 20 millions tuples > enable index update The purpose is to improve the performance. Thanks! peng
[GENERAL] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?
Hi, I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org using the same account, i got an error: There is no user by the name "myaccount". Check your spelling. Yet another page for registering at wiki.postgresql.org, named http://wiki.postgresql.org/wiki/Special:UserLogin/signup, tells me i can't signup a new account for wiki. how to register an acount for wiki.postgresql.org? Thanks! oebg
[GENERAL] Why warm-standby doesn't work using file-based log shipping method?
I try to setup warm-standby using file-based log shipping method: Master: 5432 port Standby:6432 port at same machine Master's : - wal_level = archive - archive_mode = on - archive_command = 'cp %p /home/postgres/archive/%f' Standby's restore_command = 'cp /home/postgres/archive/%f %p' 1. Firstly do a base backup for Standby ( successfuly ) [postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D /home/postgres/db/standby/pgsql/data --port=6432 LOG: database system was interrupted; last known up at 2012-02-21 17:24:33 CST LOG: starting archive recovery LOG: restored log file "00010006" from archive LOG: redo starts at 0/670 LOG: consistent recovery state reached at 0/700 cp: cannot stat `/home/postgres/archive/00010007': No such file or directory LOG: could not open file "pg_xlog/00010007" (log file 0, segment 7): No such file or directory LOG: redo done at 0/694 LOG: restored log file "00010006" from archive cp: cannot stat `/home/postgres/archive/0002.history': No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/home/postgres/archive/0001.history': No such file or directory LOG: archive recovery complete LOG: database system is ready to accept connections LOG: autovacuum launcher started 2. Then, let standby running as warm-standby mode with the following parameters in recovery.conf to enable it continously track the Master server: - standby_mode = on - restore_command = 'cp /home/postgres/archive/%f %p' [postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D /home/postgres/db/standby/pgsql/data --port=6432 LOG: database system was shut down at 2012-02-21 17:29:38 CST cp: cannot stat `/home/postgres/archive/0003.history': No such file or directory cp: cannot stat `/home/postgres/archive/0002.history': No such file or directory LOG: entering standby mode cp: cannot stat `/home/postgres/archive/00020007': No such file or directory WARNING: WAL was generated with wal_level=minimal, data may be missing HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. LOG: consistent recovery state reached at 0/700013C LOG: record with zero length at 0/700013C cp: cannot stat `/home/postgres/archive/00020007': No such file or directory cp: cannot stat `/home/postgres/archive/0003.history': No such file or directory cp: cannot stat `/home/postgres/archive/00020007': No such file or directory cp: cannot stat `/home/postgres/archive/00020007': No such file or directory cp: cannot stat `/home/postgres/archive/0003.history': No such file or directory cp: cannot stat `/home/postgres/archive/00020007': No such file or directory cp: cannot stat `/home/postgres/archive/00020007': No such file or directory 3. Then let us do some DML queries on Master, for example, I insert new tuples(large number, 100) to a table. 4. Theoreticaly new tuples could also be visible on Standby server some time later, yet after I promote the Standby to check, it reveals nothing has been down. My question is: 1. Are there anything I have missed ? 2. I still can't understand why Standby(when running as standby mode) always try to find `/home/postgres/archive/00020007' file, yet Master only copy the following files to archive directory: [postgres@localhost archive]$ cd /home/postgres/archive [postgres@localhost archive]$ ls 00010004 00010005 00010006 00010006.0020.backup 00010007 00010008 00010009 obviously there is no 00020007. Thanks!
