SPI function varchar difference between 9.0 and 9.2
| От | Achilleas Mantzios |
|---|---|
| Тема | SPI function varchar difference between 9.0 and 9.2 |
| Дата | |
| Msg-id | 1352224181.32018.YahooMailNeo@web122002.mail.ne1.yahoo.com обсуждение исходный текст |
| Список | pgsql-general |
(pls, include me in the reply, i am resending this from here since the one sent from achill(at)matrix(dot)gatewaynet(dot)com didn't make it for some reason)
#yiv1408151406 p, #yiv1408151406 li {white-space:pre-wrap;}
Hello, we are using and still maintaining a heavily modified version of DBMirror in our infrastructure,
involving one master and 80 (and growing) remote slaves, connected via UUCP over (unreliable) satelite comms.
We modified DBmirror to account for as we call it :
"Foreign Key dependency oriented, row grained, conditional, Asynchronous, Lazy replication".
Its simplicity and ease of extending were the major factors for adopting DBmirror early (circa 2004) instead of another replication
solution.
Here is the description of the specific table i am trying to insert from inside the trigger function: (those who have worked with DBMirror should be familiar)
dynacom=# \d dbmirror_pendingdata
Table "public.dbmirror_pendingdata"
Column | Type | Modifiers
--------+-------------------+-----------
seqid | integer | not null
iskey | boolean | not null
data | character varying |
Indexes:
"dbmirror_pendingdata_pkey" PRIMARY KEY, btree (seqid, iskey)
Foreign-key constraints:
"$1" FOREIGN KEY (seqid) REFERENCES dbmirror_pending(seqid) ON UPDATE CASCADE ON DELETE CASCADE
Now the problem : up to 9.0 this code used to work :
Oid planArgTypes[1] = {NAMEOID};
char *insQuery = "INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) VALUES(currval('dbmirror_pending_seqid_seq'),'f',$1)";
void *pplan;
Datum planData[1];
char *cpKeyData;
int iRetValue;
pplan = SPI_prepare(insQuery, 1, planArgTypes);
if (pplan == NULL)
{
elog(NOTICE, "Could not prepare INSERT plan");
return -1;
}
cpKeyData = packageData(....);
// ^^ this is normal NULL terminated C char *, no varlena header
planData[0] = PointerGetDatum(cpKeyData);
iRetValue = SPI_execp(pplan, planData, NULL, 1);
if (cpKeyData != 0)
pfree(cpKeyData);
if (iRetValue != SPI_OK_INSERT)
{
elog(NOTICE, "Error inserting row in pendingDelete");
return -1;
}
return 0;
The above worked fine till 9.0 (9.0 included) (although i am sure we should have updated the code to match some post 8.3 changes, ok we are guilty of this).
Trying to update a row of a table (in this example named "items") participating in the DBMirror replication e.g.
dynacom=# update items set comment = "1" where id=1637984;
is supposed to result into a row looking like the following :
dynacom=# select data from dbmirror_pendingdata where seqid = 28073232;
data
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"id"='1637984' "vslwhid"='579' "serialno"= "rh"= "lastinspdate"= "classused"='0' "classaa"= "classsurvey"= "classsurveydate"= "classduedate"= "classpostponed"= "classcomment"= "defid"='325010' "machtypecount"='1' "totalrh"='0' "comment"= "attachments"= "lastrepdate"='2011-06-27' "pmsstate"=
I am writing the above to give you an idea of how the DBmirror encoding looks like on this specific table (name "items").
Trying to run this In 9.2.1 the above code stores only varchars up to 64 bytes:
dynacom=# select data from dbmirror_pendingdata where seqid in (select seqid FROM dbmirror_pending WHERE slaveid=533) AND NOT iskey;
data
----------------------------------------------------------------------------
"id"='1377285' "vslwhid"='533' "serialno"= "rh"= "lastinspdate"=`|\x01\x08
We see that is data content is truncated after the 64th byte.
NAMEOID indeed has 64-byte length, so i changed this to :
Oid planArgTypes[1] = {VARCHAROID};
The result was a substantial delay (6 secs) in performing the update, + a major corruption of the inserted row :
dynacom=# \timing
Timing is on.
dynacom=#
dynacom=# update items set comment = "1" where id=1377285;
UPDATE 1
Time: 6272,113 ms
dynacom=# \timing
Timing is off.
dynacom=#
dynacom=# select data from dbmirror_pendingdata where seqid in (select seqid FROM dbmirror_pending WHERE slaveid=533) AND NOT iskey;
ERROR: compressed data is corrupt
dynacom=#
dynacom=#
That got me thinking that maybe this particular function storeData (which is the basic function writing out replication data) needed major overhaul.
I read a bit about the varlena functions and ended up into something like this :
Oid planArgTypes[1] = {VARCHAROID};
char *insQuery = "INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) VALUES(currval('dbmirror_pending_seqid_seq'),'f',$1)";
SPIPlanPtr pplan;
Datum planData[1];
char *cpKeyData;
char *cpKeyData_tmp;
int iRetValue;
pplan = SPI_prepare(insQuery, 1, planArgTypes);
if (pplan == NULL)
{
elog(NOTICE, "Could not prepare INSERT plan");
return -1;
}
cpKeyData = packageData(....);
// ^^ this is normal NULL terminated C char *, no varlena header
cpKeyData_tmp = palloc(VARHDRSZ+strlen(cpKeyData));
memcpy((cpKeyData_tmp+VARHDRSZ), cpKeyData, strlen(cpKeyData));
SET_VARSIZE(cpKeyData_tmp, VARHDRSZ+strlen(cpKeyData));
planData[0] = PointerGetDatum(cpKeyData_tmp);
iRetValue = SPI_execp(pplan, planData, NULL, 1);
if (cpKeyData != 0)
pfree(cpKeyData);
if (cpKeyData_tmp != 0)
pfree(cpKeyData_tmp);
if (iRetValue != SPI_OK_INSERT)
{
elog(NOTICE, "Error inserting row in storeData");
return -1;
}
return 0;
The above seems to work ok on PostgreSQL 9.2.1.
I just think that :
- the changes in the semantics of NAMEOID could be somewhere documented in the docs (postgresql-9.2.1/HISTORY)
- i believe that the documentation on how to store C strings as varchar is almost absent, i just followed the comments
in postgres.h
-
Achilleas Mantzios
IT DEPT
В списке pgsql-general по дате отправления: