Opened 11 years ago
Closed 11 years ago
#2260 closed task (fixed)
Benchmarking speed between built-in tiger normalizer and pagc_address_parser
| Reported by: | robe | Owned by: | robe |
|---|---|---|---|
| Priority: | medium | Milestone: | PostGIS 2.1.0 |
| Component: | pagc_address_parser | Version: | master |
| Keywords: | Cc: |
Description
I've started to benchmark speed/quality differences between built-in normalizer and pagc one. On a first glance it appears the built-in normalizer is faster. This may have to do with how I'm calling it, the fact that pagc I have currently compiled with debug flags — so spitting out a lot of notices, the fact that the built-in normalizer is taking advantage of indexes and doesn't need to load the lookup tables (thus less sensitive to shared memory), or a memory leak somewhere or a combination of one or more of the above and other things.
Interestingly since the pagc normalizes better, the speed slow-down in geocoding has gone up a bit so it ends up being win anyway.
So I was able to run it thru addresses I couldn't geocode before and was able to.
This suggests 2 approaches of using pagc
1) As a pure drop in replacement for existing normalizer 2) As a complementary — used to prenormalize difficult addresses.
Change History (42)
comment:1 by , 11 years ago
comment:2 by , 11 years ago
I am concerned about the 8401 W 35W regression failure if use PAGC, though I suspect this may just require adding an entry to pagc_gaz or pagc_lex to accommodate.
The Wizard of OZ one, well it is a made up address so not quite so bothered it doesn't match with what I'm expecting 
comment:3 by , 11 years ago
the 8401 W 3W case just needed more entries in lex table. So took care of this at r11290
Now output is: — 140ms (on my 64-bit windows 7) for pagc — vs. 47 ms for packaged normalizer.
address | pagc_normalize_address -----------------------------------------------------+------------------------------------------------------- 529 Main Street, Boston MA, 02129 | (529,,MAIN,St,,,Boston,MA,02129,t) 77 Massachusetts Avenue, Cambridge, MA 02139 | (77,,MASSACHUSETTS,Ave,,,Cambridge,MA,02139,t) 25 Wizard of Oz, Walaford, KS 99912323 | (25,,"WIZARD OF",,,"# OZ WALAFORD","KS 99912323",,,t) 26 Capen Street, Medford, MA | (26,,CAPEN,St,,,Medford,MA,,t) 124 Mount Auburn St, Cambridge, Massachusetts 02138 | (124,,"MOUNT AUBURN",St,,,Cambridge,MA,02138,t) 950 Main Street, Worcester, MA 01610 | (950,,MAIN,St,,,Worcester,MA,01610,t) 949 N 3rd St, New Hyde Park, NY, 11040 | (949,N,3,St,,,"New Hyde Park",NY,11040,t) 8401 W 35W Service Dr NE, Blaine, MN 55449 | (8401,W,"35 W","Svc Dr",NE,,Blaine,MN,55449,t)
Where do I disable the debug output. There is a ton of it
comment:4 by , 11 years ago
Debug out is compiled into the source.
grep 'define DEBUG' *.c
and comment them out with a ''
The new code at http://pagc.svn.sourceforge.net/viewvc/pagc/branches/sew-refactor/postgresql/ should be clean and DEBUG is off.
comment:5 by , 11 years ago
Fairer test using pagc batch mode instead of individual call, but I think I have a memory leak somewhere. Note prevent crashing on windows I had to remark out the SPI_finish call you have as mentioned in email conversations.
This query takes 47ms - 78ms on my windows 7 64-bit mingw64
SELECT *
FROM standardize_address('select * from lex', 'select * from gaz', 'select * from rules'
, 'SELECT addid As id, (p).address1 As micro, (p).city || '', '' || (p).state || '' '' || coalesce('' '' || (p).zip,'''') As macro
FROM (SELECT addid, parse_address(address) As p
FROM test_parse WHERE addid NOT IN(3) ) As t ');
output:
WARNING: transaction left non-empty SPI stack HINT: Check for missing "SPI_finish" calls. id|building|house_num|predir|qual|pretype|name|suftype|sufdir|ruralroute|extra|city|state|country|postcode|box|unit 1||529||||MAIN|STREET||||BOSTON|MASSACHUSETTS||02129|| 2||77||||MASSACHUSETTS|AVENUE||||CAMBRIDGE|MASSACHUSETTS||02139|| 4||26||||CAPEN|STREET||||MEDFORD|MASSACHUSETTS|||| 5||124||||MOUNT AUBURN|STREET||||CAMBRIDGE|MASSACHUSETTS||02138|| 6||950||||MAIN|STREET||||WORCESTER|MASSACHUSETTS||01610|| 7||949|NORTH|||3|STREET||||NEW HYDE PARK|NEW YORK||11040|| 8||35 W||||SERVICE|DRIVE|NORTHEAST|||BLAINE|MINNESOTA||55449||# 8401 W (7 rows)
I'm not sure if it helps but Jorge evidentally ran into this issue too when working with raster: http://www.postgresql.org/message-id/BANLkTi=B_y0TGh86T+Oy3XNpFqnDK006+w@mail.gmail.com
His solution seemed to be use SPI_palloc instead of palloc. http://www.postgresql.org/message-id/BANLkTi=B_y0TGh86T+Oy3XNpFqnDK006+w@mail.gmail.com
http://www.postgresql.org/docs/9.2/static/spi-spi-palloc.html
I tried replacing some but didn't seem to help.
comment:6 by , 11 years ago
I'm at a loss on this one.
This works fine on Linux. This crashes on Windows. I seems that the crash is not related to the code below, but rather something that has happened prior to this point and the server is just now recognizing the issue. I say this because it does not always crash at the same point just near this location. So sometimes a few statements before this. This is the only process using the database other than autovacuum.
When I allocate memory for std, I copy the pointer to _gp_std_ just so I could check if it is getting changed and it does not look like it is. So the big picture is:
std = calloc() do stuff free(std) -- crash!
Here is the problem code:
void std_free(STANDARDIZER *std)
{
STANDARDIZER *tmp = std;
DBG("In std_free -----------------");
if (std != _gp_std_) {
DBG("!!! std != _gp_std_ !!!");
}
if ( std == NULL ) return;
DBG("Calling close_stand_process");
if ( std -> pagc_p != NULL ) close_stand_process( std -> pagc_p ) ;
if ( std -> pagc_p -> process_errors != NULL ) {
DBG("Calling close_errors");
close_errors( std -> pagc_p -> process_errors );
DBG("Calling FREE_AND_NULL");
FREE_AND_NULL( std -> pagc_p ) ;
}
DBG("Calling close_stand_context");
close_stand_context( std -> misc_stand );
DBG("Calling free");
if (tmp != std) {
DBG("std trashed");
}
if (std == NULL) {
DBG("std is null");
}
free( std );
std = NULL ;
}
And here is my output
$ psql -U postgres -h localhost -f ../a test1
psql:../a:1: NOTICE: In std_free -----------------
psql:../a:1: NOTICE: Calling close_stand_process
psql:../a:1: NOTICE: remove_default_defs(__pagc_global__)
psql:../a:1: NOTICE: destroy_rules(__pagc_global__->rules) ;
psql:../a:1: NOTICE: destroy_rules 1
psql:../a:1: NOTICE: destroy_rules 2
psql:../a:1: NOTICE: destroy_rules 3
psql:../a:1: NOTICE: destroy_rules 4
psql:../a:1: NOTICE: destroy_rules 5
psql:../a:1: NOTICE: destroy_lexicon(__pagc_global__->addr_lexicon)
psql:../a:1: NOTICE: destroy_lexicon: i=7561
psql:../a:1: NOTICE: leaving destroy_lexicon
psql:../a:1: NOTICE: destroy_lexicon(__pagc_global__->poi_lexicon)
psql:../a:1: NOTICE: destroy_lexicon(__pagc_global__->gaz_lexicon)
psql:../a:1: NOTICE: destroy_lexicon: i=7561
psql:../a:1: NOTICE: leaving destroy_lexicon
psql:../a:1: NOTICE: Calling close_errors
psql:../a:1: NOTICE: Calling FREE_AND_NULL
psql:../a:1: NOTICE: Calling close_stand_context
psql:../a:1: NOTICE: Calling free
psql:../a:1: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:../a:1: connection to server was lost
I also tried your idea of changing palloc calls to SPI_palloc because the later reverse to palloc if it is outside an SPI context which seems safe, but as you found out it did not help.
I have also run this under valgrind on linux a while back and it reported no issues. Since I have made some changes since then maybe I should run that again.
I'm at a loss here. Does anyone have any good debugging tools under window that they could check into this with?
comment:7 by , 11 years ago
I setup a script to run this in single user mode and it more consistently fails at the same place. I also run this under valgrind in linux and while it it show various leaks in the postgresql, none of them seem to be related to this problem and may just be an atrifact of running in single user mode.
Script:
#!/bin/sh
export PROJECTS=/c/ming64/projects
export PATH=${PATH}:${PROJECTS}/pgx64/bin:${PROJECTS}/pgx64/pg92/lib
export PGDATA=${PROJECTS}/pgx64/pg92/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5441
export PGLOCALEDIR=${PROJECTS}/pgx64/pg92/locale
export GDAL_DATA=${PROJECTS}/pgx64/pg92/gdal-data
echo "select * from standardize_address('select * from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')" | ${PROJECTS}/pgx64/pg92/bin/postgres --single -D ${PGDATA} -d 2 test1
Output on Windows:
DEBUG: SlruScanDirectory invoking callback on pg_notify/0000
DEBUG: removing file "pg_notify/0000"
LOG: database system was interrupted; last known up at 2013-04-17 10:39:13 EDT
DEBUG: checkpoint record is at 0/57EB850
DEBUG: redo record is at 0/57EB850; shutdown TRUE
DEBUG: next transaction ID: 0/843; next OID: 173624
DEBUG: next MultiXactId: 1; next MultiXactOffset: 0
DEBUG: oldest unfrozen transaction ID: 665, in database 1
DEBUG: transaction ID wrap limit is 2147484312, limited by database with OID 1
LOG: database system was not properly shut down; automatic recovery in progress
DEBUG: resetting unlogged relations: cleanup 1 init 0
LOG: record with zero length at 0/57EB8B0
LOG: redo is not required
DEBUG: attempting to remove WAL segments older than log file 000000010000000000
000004
DEBUG: resetting unlogged relations: cleanup 0 init 1
PostgreSQL stand-alone backend 9.2.2
backend> LOG: statement: select * from standardize_address('select * from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')
1: id (typeid = 23, len = 4, typmod = -1, byval = t)
2: building (typeid = 1043, len = -1, typmod = -1, byval = f)
3: house_num (typeid = 1043, len = -1, typmod = -1, byval = f)
4: predir (typeid = 1043, len = -1, typmod = -1, byval = f)
5: qual (typeid = 1043, len = -1, typmod = -1, byval = f)
6: pretype (typeid = 1043, len = -1, typmod = -1, byval = f)
7: name (typeid = 1043, len = -1, typmod = -1, byval = f)
8: suftype (typeid = 1043, len = -1, typmod = -1, byval = f)
9: sufdir (typeid = 1043, len = -1, typmod = -1, byval = f)
10: ruralroute (typeid = 1043, len = -1, typmod = -1, byval = f)
11: extra (typeid = 1043, len = -1, typmod = -1, byval = f)
12: city (typeid = 1043, len = -1, typmod = -1, byval = f)
13: state (typeid = 1043, len = -1, typmod = -1, byval = f)
14: country (typeid = 1043, len = -1, typmod = -1, byval = f)
15: postcode (typeid = 1043, len = -1, typmod = -1, byval = f)
16: box (typeid = 1043, len = -1, typmod = -1, byval = f)
17: unit (typeid = 1043, len = -1, typmod = -1, byval = f)
----
NOTICE: In std_free -----------------
NOTICE: Calling close_stand_process
NOTICE: remove_default_defs(__pagc_global__)
NOTICE: destroy_rules(__pagc_global__->rules) ;
NOTICE: destroy_rules 1
NOTICE: destroy_rules 2
NOTICE: destroy_rules 3
NOTICE: destroy_rules 4
NOTICE: destroy_rules 5
NOTICE: destroy_lexicon(__pagc_global__->addr_lexicon)
NOTICE: destroy_lexicon: i=7561
NOTICE: leaving destroy_lexicon
NOTICE: destroy_lexicon(__pagc_global__->poi_lexicon)
NOTICE: destroy_lexicon(__pagc_global__->gaz_lexicon)
NOTICE: destroy_lexicon: i=7561
NOTICE: leaving destroy_lexicon
NOTICE: Calling close_errors
NOTICE: Calling FREE_AND_NULL
NOTICE: Calling close_stand_context
NOTICE: Calling free
TRAP: FailedAssertion("!(context != CurrentMemoryContext)", File: "mcxt.c", Line: 172)
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.
The Linux script for valgrind:
#!/bin/sh
export PGDATA=/etc/postgresql/9.2/main
export PGUSER=postgres
echo "select * from standardize_address('select * from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')" | sudo -u postgres valgrind --leak-check=full -v /usr/lib/postgresql/9.2/bin/postgres --single -D ${PGDATA} -d 2 test1
and the linux output (sorry this is long):
==26532== Memcheck, a memory error detector
==26532== Copyright (C) 2002-2010, and GNU GPL'd, by Julian Seward et al.
==26532== Using Valgrind-3.6.0.SVN-Debian and LibVEX; rerun with -h for copyright info
==26532== Command: /usr/lib/postgresql/9.2/bin/postgres --single -D /etc/postgresql/9.2/main -d 2 test1
==26532==
--26532-- Valgrind options:
--26532-- --suppressions=/usr/lib/valgrind/debian-libc6-dbg.supp
--26532-- --leak-check=full
--26532-- -v
--26532-- Contents of /proc/version:
--26532-- Linux version 2.6.32-5-686 (Debian 2.6.32-45) (dannf@debian.org) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Sun May 6 04:01:19 UTC 2012
--26532-- Arch and hwcaps: X86, x86-sse1-sse2
--26532-- Page sizes: currently 4096, max supported 4096
--26532-- Valgrind library directory: /usr/lib/valgrind
--26532-- Reading syms from /usr/lib/postgresql/9.2/bin/postgres (0x108000)
--26532-- Considering /usr/lib/postgresql/9.2/bin/postgres ..
--26532-- .. CRC mismatch (computed c91dc526 wanted 1a985a3b)
--26532-- Considering /usr/lib/debug/usr/lib/postgresql/9.2/bin/postgres ..
--26532-- .. CRC is valid
--26532-- Reading syms from /lib/ld-2.11.3.so (0x4000000)
--26532-- Considering /lib/ld-2.11.3.so ..
--26532-- .. CRC mismatch (computed 19231304 wanted 2b6c260a)
--26532-- Considering /usr/lib/debug/lib/ld-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /usr/lib/valgrind/memcheck-x86-linux (0x38000000)
--26532-- object doesn't have a dynamic symbol table
--26532-- Reading suppressions file: /usr/lib/valgrind/debian-libc6-dbg.supp
--26532-- Reading suppressions file: /usr/lib/valgrind/default.supp
--26532-- REDIR: 0x4016490 (index) redirected to 0x3803eda3 (vgPlain_x86_linux_REDIR_FOR_index)
--26532-- Reading syms from /usr/lib/valgrind/vgpreload_core-x86-linux.so (0x481f000)
--26532-- Reading syms from /usr/lib/valgrind/vgpreload_memcheck-x86-linux.so (0x4821000)
==26532== WARNING: new redirection conflicts with existing -- ignoring it
--26532-- new: 0x04016490 (index ) R-> 0x04824cb0 index
--26532-- REDIR: 0x4016670 (strlen) redirected to 0x48250f0 (strlen)
--26532-- Reading syms from /usr/lib/libxml2.so.2.7.8 (0x4834000)
--26532-- Considering /usr/lib/libxml2.so.2.7.8 ..
--26532-- .. CRC mismatch (computed 3b69d203 wanted 9c79e748)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/libpam.so.0.82.2 (0x495e000)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/i686/cmov/libssl.so.0.9.8 (0x496a000)
--26532-- Considering /usr/lib/i686/cmov/libssl.so.0.9.8 ..
--26532-- .. CRC mismatch (computed 0edc0877 wanted b45031c7)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0x49b6000)
--26532-- Considering /usr/lib/i686/cmov/libcrypto.so.0.9.8 ..
--26532-- .. CRC mismatch (computed 4616fd50 wanted 7c1f3f5c)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libkrb5.so.3.3 (0x4b0e000)
--26532-- Considering /usr/lib/libkrb5.so.3.3 ..
--26532-- .. CRC mismatch (computed 4c3bdaf0 wanted 4705da21)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/libcom_err.so.2.1 (0x4bc1000)
--26532-- Considering /lib/libcom_err.so.2.1 ..
--26532-- .. CRC mismatch (computed cbbefb2f wanted 8a3b70fd)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libgssapi_krb5.so.2.2 (0x4bc4000)
--26532-- Considering /usr/lib/libgssapi_krb5.so.2.2 ..
--26532-- .. CRC mismatch (computed d2208c75 wanted cac340eb)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/i686/cmov/libdl-2.11.3.so (0x4bf3000)
--26532-- Considering /lib/i686/cmov/libdl-2.11.3.so ..
--26532-- .. CRC mismatch (computed cc131032 wanted 3e040edb)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libdl-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /lib/i686/cmov/libm-2.11.3.so (0x4bf7000)
--26532-- Considering /lib/i686/cmov/libm-2.11.3.so ..
--26532-- .. CRC mismatch (computed 192a77c4 wanted 54f9f60e)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libm-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /usr/lib/libldap_r-2.4.so.2.5.6 (0x4c1d000)
--26532-- Considering /usr/lib/libldap_r-2.4.so.2.5.6 ..
--26532-- .. CRC mismatch (computed f0e46d4e wanted ed6f8005)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/i686/cmov/libc-2.11.3.so (0x4c63000)
--26532-- Considering /lib/i686/cmov/libc-2.11.3.so ..
--26532-- .. CRC mismatch (computed 54553e9a wanted b0c0c033)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libc-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /usr/lib/libz.so.1.2.3.4 (0x4daa000)
--26532-- Considering /usr/lib/libz.so.1.2.3.4 ..
--26532-- .. CRC mismatch (computed 7be92cfa wanted 329326cb)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/i686/cmov/libcrypt-2.11.3.so (0x4dbe000)
--26532-- Considering /lib/i686/cmov/libcrypt-2.11.3.so ..
--26532-- .. CRC mismatch (computed 8ecd988d wanted cc9a90a2)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libcrypt-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /usr/lib/libk5crypto.so.3.1 (0x4df0000)
--26532-- Considering /usr/lib/libk5crypto.so.3.1 ..
--26532-- .. CRC mismatch (computed 2be1e8bd wanted 50364414)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libkrb5support.so.0.1 (0x4e13000)
--26532-- Considering /usr/lib/libkrb5support.so.0.1 ..
--26532-- .. CRC mismatch (computed 0d2103d5 wanted 905a33a9)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/libkeyutils.so.1.3 (0x4e1b000)
--26532-- Considering /lib/libkeyutils.so.1.3 ..
--26532-- .. CRC mismatch (computed 48918c33 wanted 6ee49012)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /lib/i686/cmov/libresolv-2.11.3.so (0x4e1e000)
--26532-- Considering /lib/i686/cmov/libresolv-2.11.3.so ..
--26532-- .. CRC mismatch (computed 3adf14e3 wanted adbe16ef)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libresolv-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /lib/i686/cmov/libpthread-2.11.3.so (0x4e32000)
--26532-- Considering /lib/i686/cmov/libpthread-2.11.3.so ..
--26532-- .. CRC mismatch (computed c718df4e wanted 5d0f301b)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libpthread-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /usr/lib/liblber-2.4.so.2.5.6 (0x4e4b000)
--26532-- Considering /usr/lib/liblber-2.4.so.2.5.6 ..
--26532-- .. CRC mismatch (computed 460c80fc wanted 3992520f)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libsasl2.so.2.0.23 (0x4e57000)
--26532-- Considering /usr/lib/libsasl2.so.2.0.23 ..
--26532-- .. CRC mismatch (computed 8c3dfb96 wanted 7635c383)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libgnutls.so.26.14.12 (0x4e6f000)
--26532-- Considering /usr/lib/libgnutls.so.26.14.12 ..
--26532-- .. CRC mismatch (computed b0c0138f wanted 96deb7a3)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libtasn1.so.3.1.9 (0x4f07000)
--26532-- Considering /usr/lib/libtasn1.so.3.1.9 ..
--26532-- .. CRC mismatch (computed eb29a77b wanted 0c55cca5)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libgcrypt.so.11.5.3 (0x4f17000)
--26532-- Considering /usr/lib/libgcrypt.so.11.5.3 ..
--26532-- .. CRC mismatch (computed bff9339f wanted bcd4f37c)
--26532-- object doesn't have a symbol table
--26532-- Reading syms from /usr/lib/libgpg-error.so.0.4.0 (0x4f8b000)
--26532-- object doesn't have a symbol table
--26532-- REDIR: 0x4cd6130 (strlen) redirected to 0x481f42c (_vgnU_ifunc_wrapper)
--26532-- REDIR: 0x4cd5a20 (index) redirected to 0x4824c20 (index)
--26532-- REDIR: 0x4cd6e30 (memchr) redirected to 0x4825830 (memchr)
--26532-- REDIR: 0x4cd65a0 (rindex) redirected to 0x4824b60 (rindex)
--26532-- REDIR: 0x4cd61f0 (__GI_strlen) redirected to 0x48250d0 (__GI_strlen)
--26532-- REDIR: 0x4cd2920 (malloc) redirected to 0x4823ecb (malloc)
--26532-- REDIR: 0x4cd7830 (memcpy) redirected to 0x4825870 (memcpy)
--26532-- REDIR: 0x4cd5b90 (strcmp) redirected to 0x48256b0 (strcmp)
--26532-- REDIR: 0x4cd6170 (__strlen_sse2) redirected to 0x48250b0 (strlen)
--26532-- REDIR: 0x4cd64c0 (strncpy) redirected to 0x48252f0 (strncpy)
--26532-- REDIR: 0x4d41d10 (__strcpy_chk) redirected to 0x4826b30 (__strcpy_chk)
--26532-- REDIR: 0x4cd8b60 (strchrnul) redirected to 0x4826590 (strchrnul)
--26532-- REDIR: 0x4cd2840 (free) redirected to 0x4823ae5 (free)
--26532-- REDIR: 0x4cd63e0 (strncmp) redirected to 0x48255d0 (strncmp)
--26532-- REDIR: 0x4cd7520 (stpcpy) redirected to 0x4826120 (stpcpy)
--26532-- REDIR: 0x4cd7390 (mempcpy) redirected to 0x4826600 (mempcpy)
--26532-- REDIR: 0x4c91ac0 (putenv) redirected to 0x48267a0 (putenv)
--26532-- REDIR: 0x4cd62b0 (strnlen) redirected to 0x4825030 (strnlen)
--26532-- REDIR: 0x4cd38a0 (realloc) redirected to 0x4823f7a (realloc)
--26532-- REDIR: 0x4c91bf0 (unsetenv) redirected to 0x4826810 (unsetenv)
--26532-- REDIR: 0x4cd2040 (calloc) redirected to 0x48231af (calloc)
--26532-- Reading syms from /lib/i686/cmov/libnss_compat-2.11.3.so (0x4829000)
--26532-- Considering /lib/i686/cmov/libnss_compat-2.11.3.so ..
--26532-- .. CRC mismatch (computed 5450fb79 wanted f73a16d4)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libnss_compat-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /lib/i686/cmov/libnsl-2.11.3.so (0x56ba000)
--26532-- Considering /lib/i686/cmov/libnsl-2.11.3.so ..
--26532-- .. CRC mismatch (computed 80a5b503 wanted 6ed57d27)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libnsl-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- REDIR: 0x4cd5c00 (strcpy) redirected to 0x4825130 (strcpy)
--26532-- Reading syms from /lib/i686/cmov/libnss_nis-2.11.3.so (0x56d1000)
--26532-- Considering /lib/i686/cmov/libnss_nis-2.11.3.so ..
--26532-- .. CRC mismatch (computed 4426b638 wanted 169e2229)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libnss_nis-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- Reading syms from /lib/i686/cmov/libnss_files-2.11.3.so (0x56db000)
--26532-- Considering /lib/i686/cmov/libnss_files-2.11.3.so ..
--26532-- .. CRC mismatch (computed 6a294694 wanted 73ca78b0)
--26532-- Considering /usr/lib/debug/lib/i686/cmov/libnss_files-2.11.3.so ..
--26532-- .. CRC is valid
--26532-- REDIR: 0x4cd72c0 (memmove) redirected to 0x4826510 (memmove)
--26532-- REDIR: 0x4cd7330 (memset) redirected to 0x48264a0 (memset)
--26532-- REDIR: 0x4cd8a90 (rawmemchr) redirected to 0x48265c0 (rawmemchr)
2013-04-17 11:40:31 EDT DEBUG: SlruScanDirectory invoking callback on pg_notify/0000
2013-04-17 11:40:31 EDT DEBUG: removing file "pg_notify/0000"
2013-04-17 11:40:32 EDT LOG: database system was shut down at 2013-04-17 11:36:15 EDT
2013-04-17 11:40:32 EDT DEBUG: checkpoint record is at 0/2C437D8
2013-04-17 11:40:32 EDT DEBUG: redo record is at 0/2C437D8; shutdown TRUE
2013-04-17 11:40:32 EDT DEBUG: next transaction ID: 0/854; next OID: 49540
2013-04-17 11:40:32 EDT DEBUG: next MultiXactId: 1; next MultiXactOffset: 0
2013-04-17 11:40:32 EDT DEBUG: oldest unfrozen transaction ID: 671, in database 1
2013-04-17 11:40:32 EDT DEBUG: transaction ID wrap limit is 2147484318, limited by database with OID 1
--26532-- REDIR: 0x4cd6fd0 (bcmp) redirected to 0x4826080 (bcmp)
==26532== Syscall param write(buf) points to uninitialised byte(s)
==26532== at 0x4D200F3: __write_nocancel (syscall-template.S:82)
==26532== by 0x4CC982E: new_do_write (fileops.c:530)
==26532== by 0x4CC9B45: _IO_do_write@@GLIBC_2.1 (fileops.c:503)
==26532== by 0x4CCA62C: _IO_file_overflow@@GLIBC_2.1 (fileops.c:881)
==26532== by 0x4CC9977: _IO_file_xsputn@@GLIBC_2.1 (fileops.c:1358)
==26532== by 0x4CBF77D: fwrite (iofwrite.c:45)
==26532== by 0x499A36: write_item (relcache.c:4487)
==26532== by 0x499C42: write_relcache_init_file (relcache.c:4369)
==26532== by 0x49CC88: RelationCacheInitializePhase3 (relcache.c:3085)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532== Address 0x48314a2 is not stack'd, malloc'd or (recently) free'd
==26532==
==26532== Syscall param write(buf) points to uninitialised byte(s)
==26532== at 0x4D200F3: __write_nocancel (syscall-template.S:82)
==26532== by 0x4CC982E: new_do_write (fileops.c:530)
==26532== by 0x4CC9B45: _IO_do_write@@GLIBC_2.1 (fileops.c:503)
==26532== by 0x4CCB06F: _IO_file_close_it@@GLIBC_2.1 (fileops.c:170)
==26532== by 0x4CBE457: fclose@@GLIBC_2.1 (iofclose.c:62)
==26532== by 0x398505: FreeDesc (fd.c:1533)
==26532== by 0x499D25: write_relcache_init_file (relcache.c:4435)
==26532== by 0x49CC88: RelationCacheInitializePhase3 (relcache.c:3085)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532== Address 0x48310e2 is not stack'd, malloc'd or (recently) free'd
==26532==
PostgreSQL stand-alone backend 9.2.4
backend> 2013-04-17 11:40:33 EDT LOG: statement: select * from standardize_address('select * from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')
1: id (typeid = 23, len = 4, typmod = -1, byval = t)
2: building (typeid = 1043, len = -1, typmod = -1, byval = f)
3: house_num (typeid = 1043, len = -1, typmod = -1, byval = f)
4: predir (typeid = 1043, len = -1, typmod = -1, byval = f)
5: qual (typeid = 1043, len = -1, typmod = -1, byval = f)
6: pretype (typeid = 1043, len = -1, typmod = -1, byval = f)
7: name (typeid = 1043, len = -1, typmod = -1, byval = f)
8: suftype (typeid = 1043, len = -1, typmod = -1, byval = f)
9: sufdir (typeid = 1043, len = -1, typmod = -1, byval = f)
10: ruralroute (typeid = 1043, len = -1, typmod = -1, byval = f)
11: extra (typeid = 1043, len = -1, typmod = -1, byval = f)
12: city (typeid = 1043, len = -1, typmod = -1, byval = f)
13: state (typeid = 1043, len = -1, typmod = -1, byval = f)
14: country (typeid = 1043, len = -1, typmod = -1, byval = f)
15: postcode (typeid = 1043, len = -1, typmod = -1, byval = f)
16: box (typeid = 1043, len = -1, typmod = -1, byval = f)
17: unit (typeid = 1043, len = -1, typmod = -1, byval = f)
----
--26532-- REDIR: 0x4cd5870 (strcat) redirected to 0x4824d40 (strcat)
--26532-- Reading syms from /usr/lib/postgresql/9.2/lib/address_standardizer.so (0x73fb000)
--26532-- Reading syms from /lib/libpcre.so.3.12.1 (0x7464000)
--26532-- Considering /lib/libpcre.so.3.12.1 ..
--26532-- .. CRC mismatch (computed 32c7458a wanted 7e596bef)
--26532-- object doesn't have a symbol table
--26532-- REDIR: 0x4016ae0 (stpcpy) redirected to 0x48262e0 (stpcpy)
==26532== Conditional jump or move depends on uninitialised value(s)
==26532== at 0x7401C70: copy_best (analyze.c:1175)
==26532== by 0x7401B9B: save_current_composition (analyze.c:1154)
==26532== by 0x7401979: deposit_stz (analyze.c:1035)
==26532== by 0x74017D9: shallow_clause_scan (analyze.c:955)
==26532== by 0x7401073: evaluator (analyze.c:575)
==26532== by 0x7408A4B: _Close_Stand_Field_ (standard.c:679)
==26532== by 0x740733B: standardize_field (standard.c:118)
==26532== by 0x74082E6: std_standardize_mm (standard.c:464)
==26532== by 0x73FF252: fetch_stdaddr (address_standardizer.c:179)
==26532== by 0x740009B: standardize_address (address_standardizer.c:559)
==26532== by 0x2D456F: ExecMakeTableFunctionResult (execQual.c:2156)
==26532== by 0x2E70D3: FunctionNext (nodeFunctionscan.c:65)
==26532==
1: id = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
3: house_num = "123" (typeid = 1043, len = -1, typmod = -1, byval = f)
7: name = "MAIN" (typeid = 1043, len = -1, typmod = -1, byval = f)
8: suftype = "STREET" (typeid = 1043, len = -1, typmod = -1, byval = f)
12: city = "KANSAS CITY" (typeid = 1043, len = -1, typmod = -1, byval = f)
13: state = "MISSOURI" (typeid = 1043, len = -1, typmod = -1, byval = f)
15: postcode = "45678" (typeid = 1043, len = -1, typmod = -1, byval = f)
----
backend> 2013-04-17 11:40:36 EDT LOG: shutting down
2013-04-17 11:40:36 EDT DEBUG: SlruScanDirectory invoking callback on pg_multixact/offsets/0000
2013-04-17 11:40:36 EDT DEBUG: SlruScanDirectory invoking callback on pg_multixact/members/0000
2013-04-17 11:40:36 EDT DEBUG: attempting to remove WAL segments older than log file 000000010000000000000001
2013-04-17 11:40:36 EDT DEBUG: SlruScanDirectory invoking callback on pg_subtrans/0000
2013-04-17 11:40:36 EDT LOG: database system is shut down
--26532-- Discarding syms at 0x4829e30-0x482ebc8 in /lib/i686/cmov/libnss_compat-2.11.3.so due to munmap()
--26532-- Discarding syms at 0x56d2930-0x56d8708 in /lib/i686/cmov/libnss_nis-2.11.3.so due to munmap()
--26532-- Discarding syms at 0x56bd190-0x56c9de8 in /lib/i686/cmov/libnsl-2.11.3.so due to munmap()
--26532-- Discarding syms at 0x56dca90-0x56e40d8 in /lib/i686/cmov/libnss_files-2.11.3.so due to munmap()
==26532==
==26532== HEAP SUMMARY:
==26532== in use at exit: 1,066,502 bytes in 296 blocks
==26532== total heap usage: 28,351 allocs, 28,055 frees, 9,484,874 bytes allocated
==26532==
==26532== Searching for pointers to 296 not-freed blocks
==26532== Checked 1,375,940 bytes
==26532==
==26532== 160 (40 direct, 120 indirect) bytes in 1 blocks are definitely lost in loss record 62 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4D3FDD3: nss_parse_service_list (nsswitch.c:622)
==26532== by 0x4D40516: __nss_database_lookup (nsswitch.c:164)
==26532== by 0x482AEAB: ???
==26532== by 0x482BB6C: ???
==26532== by 0x4CF9584: getpwuid_r@@GLIBC_2.1.2 (getXXbyYY_r.c:253)
==26532== by 0x4CF8EEE: getpwuid (getXXbyYY.c:117)
==26532== by 0x306720: main (main.c:382)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 71 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDC6: RelationCacheInitializePhase3 (relcache.c:2911)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 72 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDD5: RelationCacheInitializePhase3 (relcache.c:2913)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 73 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDE4: RelationCacheInitializePhase3 (relcache.c:2915)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 74 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDF3: RelationCacheInitializePhase3 (relcache.c:2917)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 75 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CE02: RelationCacheInitializePhase3 (relcache.c:2919)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 76 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CE11: RelationCacheInitializePhase3 (relcache.c:2921)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 77 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CE20: RelationCacheInitializePhase3 (relcache.c:2923)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 78 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CD66: RelationCacheInitializePhase3 (relcache.c:2943)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 79 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CD75: RelationCacheInitializePhase3 (relcache.c:2945)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 80 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CD84: RelationCacheInitializePhase3 (relcache.c:2947)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 81 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CD93: RelationCacheInitializePhase3 (relcache.c:2949)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 82 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDA2: RelationCacheInitializePhase3 (relcache.c:2951)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 83 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x1959A2: systable_beginscan (genam.c:259)
==26532== by 0x49C1FF: RelationBuildDesc (relcache.c:3212)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 84 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x1959A2: systable_beginscan (genam.c:259)
==26532== by 0x497FB5: RelationGetIndexList (relcache.c:3382)
==26532== by 0x35CAB7: get_relation_info (plancat.c:137)
==26532== by 0x35F81C: build_simple_rel (relnode.c:131)
==26532==
==26532== 1,024 bytes in 1 blocks are possibly lost in loss record 85 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x191BAB: toast_fetch_datum (tuptoaster.c:1627)
==26532== by 0x1922AD: toast_flatten_tuple (tuptoaster.c:980)
==26532== by 0x492BDE: CatalogCacheCreateEntry (catcache.c:1646)
==26532== by 0x494312: SearchCatCache (catcache.c:1190)
==26532==
==26532== 1,688 bytes in 1 blocks are possibly lost in loss record 87 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9BE8: AllocSetAlloc (aset.c:581)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x3AF947: InitDeadLockChecking (deadlock.c:171)
==26532== by 0x3ACFB2: InitProcess (proc.c:415)
==26532== by 0x3C04F8: PostgresMain (postgres.c:3670)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 2,063 (84 direct, 1,979 indirect) bytes in 1 blocks are definitely lost in loss record 89 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C7F23: save_ps_display_args (ps_status.c:164)
==26532== by 0x305CE0: main (main.c:82)
==26532==
==26532== 2,520 bytes in 1 blocks are possibly lost in loss record 91 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9BE8: AllocSetAlloc (aset.c:581)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x3AF8AB: InitDeadLockChecking (deadlock.c:142)
==26532== by 0x3ACFB2: InitProcess (proc.c:415)
==26532== by 0x3C04F8: PostgresMain (postgres.c:3670)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 3,072 bytes in 3 blocks are possibly lost in loss record 92 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x35CB23: get_relation_info (plancat.c:164)
==26532== by 0x35F81C: build_simple_rel (relnode.c:131)
==26532== by 0x344397: add_base_rels_to_query (initsplan.c:91)
==26532== by 0x3443BD: add_base_rels_to_query (initsplan.c:99)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 102 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x49B0BD: RelationInitIndexAccessInfo (relcache.c:1224)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CDC6: RelationCacheInitializePhase3 (relcache.c:2911)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 103 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4CAA01: MemoryContextCreate (mcxt.c:522)
==26532== by 0x4C9932: AllocSetContextCreate (aset.c:362)
==26532== by 0x493041: CreateCacheMemoryContext (catcache.c:537)
==26532== by 0x49D90B: RelationCacheInitialize (relcache.c:2754)
==26532== by 0x4B6AB4: InitPostgres (postinit.c:532)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 104 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x49D8F5: RelationCacheInitialize (relcache.c:2763)
==26532== by 0x4B6AB4: InitPostgres (postinit.c:532)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 105 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x493147: InitCatCache (catcache.c:762)
==26532== by 0x49F83F: InitCatalogCache (syscache.c:794)
==26532== by 0x4B6AB9: InitPostgres (postinit.c:533)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 106 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x4CB95C: EnablePortalManager (portalmem.c:121)
==26532== by 0x4B6AC4: InitPostgres (postinit.c:537)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 107 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x3BA406: smgropen (smgr.c:149)
==26532== by 0x395B9E: RelationGetNumberOfBlocksInFork (bufmgr.c:1981)
==26532== by 0x18C3A0: initscan (heapam.c:118)
==26532== by 0x18C613: heap_beginscan_internal (heapam.c:1258)
==26532== by 0x18C725: heap_beginscan (heapam.c:1176)
==26532== by 0x4B7054: InitPostgres (postinit.c:997)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 108 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x3ABA9A: LockAcquireExtended (lock.c:747)
==26532== by 0x3AC21B: LockAcquire (lock.c:662)
==26532== by 0x3A7AF8: LockRelationOid (lmgr.c:79)
==26532== by 0x18C8EB: relation_open (heapam.c:914)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x1959A2: systable_beginscan (genam.c:259)
==26532== by 0x49AEE0: RelationInitIndexAccessInfo (relcache.c:1290)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 109 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x2F4AF1: initStringInfo (stringinfo.c:50)
==26532== by 0x3C065E: PostgresMain (postgres.c:3872)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 110 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x4A46B8: lookup_type_cache (typcache.c:164)
==26532== by 0x4A4C76: lookup_rowtype_tupdesc_internal (typcache.c:693)
==26532== by 0x4A4DD3: lookup_rowtype_tupdesc_copy (typcache.c:767)
==26532== by 0x4B25C2: internal_get_result_type (funcapi.c:371)
==26532== by 0x24B718: addRangeTableEntryForFunction (parse_relation.c:1126)
==26532== by 0x237C88: transformFromClauseItem (parse_clause.c:600)
==26532== by 0x23830B: transformFromClause (parse_clause.c:129)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 111 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x4CB65C: CreatePortal (portalmem.c:218)
==26532== by 0x3BF5A4: exec_simple_query (postgres.c:988)
==26532== by 0x3C087B: PostgresMain (postgres.c:3959)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 112 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x4AF76E: fmgr_info_cxt_security (fmgr.c:546)
==26532== by 0x2D1E1E: init_fcache (execQual.c:1307)
==26532== by 0x2D4C74: ExecMakeTableFunctionResult (execQual.c:2080)
==26532== by 0x2E70D3: FunctionNext (nodeFunctionscan.c:65)
==26532== by 0x2D536D: ExecScan (execScan.c:82)
==26532== by 0x2E7040: ExecFunctionScan (nodeFunctionscan.c:104)
==26532== by 0x2CCCF7: ExecProcNode (execProcnode.c:425)
==26532==
==26532== 8,192 bytes in 1 blocks are possibly lost in loss record 113 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B3BB9: hash_create (dynahash.c:296)
==26532== by 0x49EF85: get_tablespace_page_costs (spccache.c:85)
==26532== by 0x3313D9: cost_seqscan (costsize.c:197)
==26532== by 0x35ADCD: create_seqscan_path (pathnode.c:742)
==26532== by 0x32D0CE: set_rel_pathlist (allpaths.c:379)
==26532== by 0x32D630: make_one_rel (allpaths.c:204)
==26532== by 0x345021: query_planner (planmain.c:259)
==26532== by 0x346F61: grouping_planner (planner.c:1221)
==26532==
==26532== 16,384 bytes in 1 blocks are possibly lost in loss record 115 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B30C0: element_alloc (dynahash.c:1367)
==26532== by 0x4B3507: hash_search_with_hash_value (dynahash.c:1015)
==26532== by 0x4B39CA: hash_search (dynahash.c:805)
==26532== by 0x4A47F1: lookup_type_cache (typcache.c:200)
==26532== by 0x4A4C76: lookup_rowtype_tupdesc_internal (typcache.c:693)
==26532== by 0x4A4DD3: lookup_rowtype_tupdesc_copy (typcache.c:767)
==26532== by 0x4B25C2: internal_get_result_type (funcapi.c:371)
==26532== by 0x24B718: addRangeTableEntryForFunction (parse_relation.c:1126)
==26532==
==26532== 32,768 bytes in 1 blocks are possibly lost in loss record 117 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C99B0: AllocSetContextCreate (aset.c:410)
==26532== by 0x1AAC01: StartTransactionCommand (xact.c:835)
==26532== by 0x4B6FE4: InitPostgres (postinit.c:576)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 49,152 bytes in 48 blocks are possibly lost in loss record 118 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x49ACB6: RelationInitIndexAccessInfo (relcache.c:1054)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x492F3A: InitCatCachePhase2 (catcache.c:967)
==26532== by 0x49F251: InitCatalogCachePhase2 (syscache.c:828)
==26532== by 0x49CC74: RelationCacheInitializePhase3 (relcache.c:3079)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532==
==26532== 70,328 bytes in 1 blocks are possibly lost in loss record 119 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9BE8: AllocSetAlloc (aset.c:581)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x4B3B66: DynaHashAlloc (dynahash.c:226)
==26532== by 0x4B30C0: element_alloc (dynahash.c:1367)
==26532== by 0x4B3E57: hash_create (dynahash.c:447)
==26532== by 0x4DD928: pg_tzset (pgtz.c:193)
==26532== by 0x4DD96F: pg_timezone_initialize (pgtz.c:311)
==26532== by 0x4C6F37: InitializeGUCOptions (guc.c:3850)
==26532== by 0x3C1F1A: PostgresMain (postgres.c:3546)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 114,688 bytes in 3 blocks are possibly lost in loss record 120 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x4930B0: InitCatCache (catcache.c:776)
==26532== by 0x49F83F: InitCatalogCache (syscache.c:794)
==26532== by 0x4B6AB9: InitPostgres (postinit.c:533)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 131,072 bytes in 1 blocks are possibly lost in loss record 121 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x49B98E: RelationBuildDesc (relcache.c:338)
==26532== by 0x49C919: load_critical_index (relcache.c:3109)
==26532== by 0x49CE02: RelationCacheInitializePhase3 (relcache.c:2919)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 151,768 bytes in 14 blocks are possibly lost in loss record 122 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9BE8: AllocSetAlloc (aset.c:581)
==26532== by 0x4CA927: MemoryContextAllocZero (mcxt.c:600)
==26532== by 0x4930B0: InitCatCache (catcache.c:776)
==26532== by 0x49F83F: InitCatalogCache (syscache.c:794)
==26532== by 0x4B6AB9: InitPostgres (postinit.c:533)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532==
==26532== 262,144 bytes in 1 blocks are possibly lost in loss record 123 of 123
==26532== at 0x4823F50: malloc (vg_replace_malloc.c:236)
==26532== by 0x4C9C75: AllocSetAlloc (aset.c:746)
==26532== by 0x4CA821: MemoryContextAlloc (mcxt.c:577)
==26532== by 0x49ABCC: RelationInitIndexAccessInfo (relcache.c:1025)
==26532== by 0x49C600: RelationBuildDesc (relcache.c:915)
==26532== by 0x49D85C: RelationIdGetRelation (relcache.c:1581)
==26532== by 0x18C8AB: relation_open (heapam.c:917)
==26532== by 0x196A9B: index_open (indexam.c:157)
==26532== by 0x492F3A: InitCatCachePhase2 (catcache.c:967)
==26532== by 0x49F251: InitCatalogCachePhase2 (syscache.c:828)
==26532== by 0x49CC74: RelationCacheInitializePhase3 (relcache.c:3079)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532==
==26532== LEAK SUMMARY:
==26532== definitely lost: 124 bytes in 2 blocks
==26532== indirectly lost: 2,099 bytes in 30 blocks
==26532== possibly lost: 949,248 bytes in 102 blocks
==26532== still reachable: 115,031 bytes in 162 blocks
==26532== suppressed: 0 bytes in 0 blocks
==26532== Reachable blocks (those to which a pointer was found) are not shown.
==26532== To see them, rerun with: --leak-check=full --show-reachable=yes
==26532==
==26532== Use --track-origins=yes to see where uninitialised values come from
==26532== ERROR SUMMARY: 65 errors from 43 contexts (suppressed: 79 from 15)
==26532==
==26532== 2 errors in context 1 of 43:
==26532== Conditional jump or move depends on uninitialised value(s)
==26532== at 0x7401C70: copy_best (analyze.c:1175)
==26532== by 0x7401B9B: save_current_composition (analyze.c:1154)
==26532== by 0x7401979: deposit_stz (analyze.c:1035)
==26532== by 0x74017D9: shallow_clause_scan (analyze.c:955)
==26532== by 0x7401073: evaluator (analyze.c:575)
==26532== by 0x7408A4B: _Close_Stand_Field_ (standard.c:679)
==26532== by 0x740733B: standardize_field (standard.c:118)
==26532== by 0x74082E6: std_standardize_mm (standard.c:464)
==26532== by 0x73FF252: fetch_stdaddr (address_standardizer.c:179)
==26532== by 0x740009B: standardize_address (address_standardizer.c:559)
==26532== by 0x2D456F: ExecMakeTableFunctionResult (execQual.c:2156)
==26532== by 0x2E70D3: FunctionNext (nodeFunctionscan.c:65)
==26532==
==26532==
==26532== 2 errors in context 2 of 43:
==26532== Syscall param write(buf) points to uninitialised byte(s)
==26532== at 0x4D200F3: __write_nocancel (syscall-template.S:82)
==26532== by 0x4CC982E: new_do_write (fileops.c:530)
==26532== by 0x4CC9B45: _IO_do_write@@GLIBC_2.1 (fileops.c:503)
==26532== by 0x4CCB06F: _IO_file_close_it@@GLIBC_2.1 (fileops.c:170)
==26532== by 0x4CBE457: fclose@@GLIBC_2.1 (iofclose.c:62)
==26532== by 0x398505: FreeDesc (fd.c:1533)
==26532== by 0x499D25: write_relcache_init_file (relcache.c:4435)
==26532== by 0x49CC88: RelationCacheInitializePhase3 (relcache.c:3085)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532== Address 0x48310e2 is not stack'd, malloc'd or (recently) free'd
==26532==
==26532==
==26532== 21 errors in context 3 of 43:
==26532== Syscall param write(buf) points to uninitialised byte(s)
==26532== at 0x4D200F3: __write_nocancel (syscall-template.S:82)
==26532== by 0x4CC982E: new_do_write (fileops.c:530)
==26532== by 0x4CC9B45: _IO_do_write@@GLIBC_2.1 (fileops.c:503)
==26532== by 0x4CCA62C: _IO_file_overflow@@GLIBC_2.1 (fileops.c:881)
==26532== by 0x4CC9977: _IO_file_xsputn@@GLIBC_2.1 (fileops.c:1358)
==26532== by 0x4CBF77D: fwrite (iofwrite.c:45)
==26532== by 0x499A36: write_item (relcache.c:4487)
==26532== by 0x499C42: write_relcache_init_file (relcache.c:4369)
==26532== by 0x49CC88: RelationCacheInitializePhase3 (relcache.c:3085)
==26532== by 0x4B6E2E: InitPostgres (postinit.c:824)
==26532== by 0x3C0538: PostgresMain (postgres.c:3683)
==26532== by 0x306755: main (main.c:197)
==26532== Address 0x48314a2 is not stack'd, malloc'd or (recently) free'd
==26532==
--26532--
--26532-- used_suppression: 79 dl-hack3-cond-1
==26532==
==26532== ERROR SUMMARY: 65 errors from 43 contexts (suppressed: 79 from 15)
comment:8 by , 11 years ago
Make sure to compile PostgreSQL with —enable-cassert. That will have PostgreSQL be very strict about memory contexts.
comment:9 by , 11 years ago
Correct, and I amd seeing the following in the logfile:
TRAP: FailedAssertion("!(context != CurrentMemoryContext)", File: "mcxt.c", Line
: 172)
^M
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.^M
LOG: server process (PID 7776) exited with exit code 3^M
DETAIL: Failed process was running: select * from standardize_address('select *
from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id
, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')
;^M
LOG: terminating any other active server processes^M
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the curre
nt transaction and exit, because another server process exited abnormally and po
ssibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat yo
ur command.
LOG: all server processes terminated; reinitializing^M
LOG: database system was interrupted; last known up at 2013-04-16 23:30:32 EDT
FATAL: the database system is in recovery mode
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/57E89B8
LOG: redo is not required
LOG: database system is ready to accept connections^M
LOG: autovacuum launcher started
comment:10 by , 11 years ago
I just integrated a test_main target to the makefile so you can not run the address standardizer (not parseaddress) outside of the database in the src directory as it reads some support files from there.
I ran valgrind on this and it is clean on the linux system. I'll try to build it in minggw and see if it runs there or throws an error.
You can ru it like this:
make test_main ./test_main 123 cambridge st boston ma 01002 exit
comment:12 by , 11 years ago
And test_main crashes on ming64.
And test_main crashes on ming32.
And I have no idea how to debug this in windows short of LOTS of print statements 
comment:13 by , 11 years ago
ok, found gdb for mingw and the problem is not related to the postgresql crash. Evidently, on linux passing a null pointer to printf prints "(null)" but crashes in mingw.
So I ran postgres in single user mode and in gdb and I'm reminded that it runs fine and fails with a TRAP related to memory contexts which has nothing to do with the address standardizer but rather with the pg memory management and how the library is wrapped.
comment:14 by , 11 years ago
Yah I should probably research on tools for debugging in windows. I'm guessing the reason why removing SP_finish prevents it from crashing is that SP_finish is trying to clean memory it thinks is under pg memory management and over steps the address space thus triggering a "No No not yours" error in. I tried playing with those SP_pop, SP_push functions but got no where with those.
I guess worst comes to worse I could use the old library version before you merged everything into one folder. That one works fine.
comment:15 by , 11 years ago
I'm pretty sure the issue is not the case of pfree trying to free memory allocated by malloc because we would have the same issue on Linux. Linux runs in valgrind without any leaks or errors. This has something to do with palloc and SPI_palloc or something out the SPI context being requested to be pfree in SPI_pfree. I'm still a little weak when it comes to understanding what the various pg memory contexts are and how to differentiate them.
comment:16 by , 11 years ago
Steve,
Sorry this is a little embarassing, I just tried the old version and it crashed as well. Then I realized the new one actually works on my EDB install just fine like I thought it did before. I think I was testing with a NULL address when I thought it didn't work (like sample above), and like you said it crashes on NULL in windows.
So it seems the mingw one is probably failing because Cassert is enabled in my mingw install and you always saw failure because you always test in mingw (and I always swap back to VC++ one because I know that is what people will be using) and my VC EDB is not compiled with cassert because its production version. I'll do a couple more tests to make sure we are not seeing things and also recompile a mingw version without cassert enabled to see if it works.
That said is your Linux version compiled with cassert enabled?
comment:17 by , 11 years ago
My Linux versions is from:
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
So I suspect that that it is not. I'll also recompile pg 9.2.2 without cassert enabled on ming64.
comment:18 by , 11 years ago
I just recompiled without cassert enabled and I'm still getting the TRAP in the logfile and it crashes.
$ ./config.status -V PostgreSQL config.status 9.2.2 configured by ./configure, generated by GNU Autoconf 2.63, with options "'--prefix=/c/ming64/projects/pgx64/pg92' '--build=x86_64-w64-min gw32' '--host=x86_64-w64-mingw32' '--target=x86_64-w64-mingw32' '--with-pgport=5 441' '--enable-debug' '--enable-integer-datetimes' '--disable-float8-byval' '--w ithout-zlib' 'build_alias=x86_64-w64-mingw32' 'host_alias=x86_64-w64-mingw32' 't arget_alias=x86_64-w64-mingw32'"
And here is the logfile:
TRAP: FailedAssertion("!(context != CurrentMemoryContext)", File: "mcxt.c", Line
: 172)
^M
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.^M
LOG: server process (PID 3632) exited with exit code 3^M
DETAIL: Failed process was running: select * from standardize_address('select *
from lex', 'select * from gaz', 'select * from rules', 'select 1::integer as id
, ''123 Main Street''::text as micro, ''Kansas City, MO 45678''::text as macro')
;^M
LOG: terminating any other active server processes^M
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the curre
nt transaction and exit, because another server process exited abnormally and po
ssibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat yo
ur command.
LOG: all server processes terminated; reinitializing^M
LOG: database system was interrupted; last known up at 2013-04-20 20:47:35 EDT
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/57EBD90
LOG: redo is not required
LOG: database system is ready to accept connections^M
LOG: autovacuum launcher started
comment:19 by , 11 years ago
Regarding above — what happens if you take out the —enable-debug recompiling mine withot cassert or debug but haven't retested yet.
okay I stand corrected about prior statement, its a bit more complicated.
1) Both old and new fail in my mingw64 with cassert enabled 2) On VC EDB install with my last sample test:
SELECT *
FROM standardize_address('select * from lex', 'select * from gaz', 'select * from rules'
, 'SELECT addid As id, (p).address1 As micro, (p).city || '', '' || (p).state || '' '' || coalesce('' '' || (p).zip,'''') As macro
FROM (SELECT addid, parse_address(address) As p
FROM test_parse WHERE addid NOT IN(3) ) As t ');
above works fine but when wrapped into my plpgsql pagc_normalize_address packaged with tiger geocoder:
SELECT address, pagc_normalize_address(address) FROM test_parse;
old works new crashes.
Can you try the tiger goecoder pagc_normalize_address on your linux.
If you don't have 2.1 compiled just pull the share/extension/postgis_tiger_geocoder* files from
http://winnie.postgis.net/download/windows/pg92/buildbot/ (should work on your Linux as well since its all plpgsql)
CREATE EXTENSION postgis_tiger_geocoder;
Then you should be able to run the above test on linux as well and HOPEFULLY we'll find a similar issue on Linux that is easier to debug than it is on windows.
comment:20 by , 11 years ago
Okay the good news. All tests on my mingw64 postgresql pass if I compile without the debug or cassert flags.
So building like this:
-- No crash
./configure --prefix=${PROJECTS}/pgx64/pg${PG_VER} \
--build=x86_64-w64-mingw32 \
--host=x86_64-w64-mingw32 --target=x86_64-w64-mingw32 \
--with-pgport=8442 --disable-float8-byval \
--enable-integer-datetimes --without-zlib
building like this:
-- Crashes --
./configure --prefix=${PROJECTS}/pgx64/pg${PG_VER} \
--build=x86_64-w64-mingw32 \
--host=x86_64-w64-mingw32 --target=x86_64-w64-mingw32 \
--with-pgport=8442 --disable-float8-byval --enable-cassert --enable-debug \
--enable-integer-datetimes --without-zlib
So either the mingw has a bug in the debugging/or cassert logic or its catching things your Linux one isn't because your linux is not compiled with debug and cassert flags.
comment:21 by , 11 years ago
ok, I just rebuilt 9.2.2 without —enable-cassert —enable-debug and it does not crash now. YEAH!
Ok, next I'll recompile ming32 pg9.2 and verify that works also.
I'll grab you files tomorrow and try them on Linux, my brain is too fried to tackle it tonight. Thanks for the help, it is appreciated.
comment:22 by , 11 years ago
Ok posted the problem on the pgsql-hackers list and Tom Lane replied with:
You can't hold a SPI context open across multiple calls of an SRF. Even if it somehow failed to malfunction in isolation, this would certainly not work in a query where some other called function was also using SPI.
Possibly the reason the code accidentally fails to malfunction on Linux is you're not using an —enable-cassert build there? That would wipe freed memory and thus help to reveal errors of this sort consistently, whereas otherwise the failures would be context-dependent.
So I guess I have to figure out how to save the contents of my SPI_tuptable into another context that can be held across SRF calls.
comment:23 by , 11 years ago
Okay that possibly explains why it eventually crashes when I wrapped it in my plpgsql function under VC++ build but seems to work fine outside of the function.
As I recall I don't think the last one did though (I also had to change my code because I was getting a weird error when I wrapped it in the plpgsql function something like function did not return suitable structure.
Well the ultimate solution would be to not have it set returning at all and just maintain the state of the rules/gaz/lex by matching if the SQL statements of those are the same — which gets back to Paul's proposal of "do it like I do ST_Intersects"
So in that model your prepared geom (or rather constant object would be the rules/gaz/lex)
Whatever call would check to see if the sqls of these match and use the same if it does otherwise builds a new set and throws away the old. (Boy do I wihsi I understood Paul's code . That would make your code much more flexible too since one would not have to pass in an SQL statement and therefore could possibly skip a join and I wouldn't have to create a special batch version .
comment:24 by , 11 years ago
That is my conclusion also. So I'm working on rewriting this. I have figured out how to create the cache. I need to figure out how to hook the per query shutdown callback using RegisterExprContextCallback().
I have decided to change the signature to:
standardize_address(lextab text, gaztab text, rultab text, micro text, macro text)
Where *tab are table or view names and internal I'll do a 'select * from tab'.
I don't think I need to cache multiple contexts because these are done on a per query contexts and the lex, gaz, and rules will get loaded into the standardizer on creation when the first record is processed and will be held through out the query then released.
If there are multiple queries, each query will run in its own memory context and have its own standardizer cached in that context and the queries will not share contexts.
comment:25 by , 11 years ago
Well I guess in theory I can wrap my (SELECT .. FROM somelex) As lextab and that will still work with your above signatures. Only issue I see with taking just the name is a user may add other side line columns that just add extra baggage to your query e.g. my is_custom or whatever column.
On other note and can't assume you can use the same set.
The reason I say you need to check is that it is quite possible (though rare except for someone as perverted as me ) that someone might do something like this:
{{{SELECT a.id, standardize_address(c.country_lex, c.country_gaz, c.country_rule, a.micro, a.macro) FROM myaddresses AS a LEFT JOIN country_config As c ON (a.country = c.country); }}}
And will be very puzzled when the wrong parse tables are used. It might be slow except if I order by country so all records of same country use the same parser sets.
comment:26 by , 11 years ago
fell out of code tag
SELECT a.id, standardize_address(c.country_lex, c.country_gaz, c.country_rule, a.micro, a.macro)
FROM myaddresses AS a LEFT JOIN country_config As c ON (a.country = c.country);
comment:27 by , 11 years ago
OK, New code has been checked to pagc/branches/sew-refactor/postgresql/
- Now has a cache for the standardizer at the query level
- Function signature has changed
- Runs on ming32 pg9.2.3 built with —enable-cassert —enable-debug
- Has one minor but annoying issue on linux that I need to look into
- installs as CREATE EXTENSION address_standardizer;
- psql -U postgres -h localhost -f test1.sql testdb (might have path issues)
- psql -U postgres -h localhost -f test2.sql testdb (might have path issues)
Then signature is:
* The signature for standardize_address follows. The lextab, gaztab and * rultab should not change once the reference has been standardized and * the same tables must be used for a geocode request as were used on the * reference set or the matching will get degregated. * * select * from standardize_address( * lextab text, -- name of table of view * gaztab text, -- name of table or view * rultab text, -- name of table of view * micro text, -- '123 main st' * macro text); -- 'boston ma 01002' * * If you want to standardize a whole table then call it like: * * insert into stdaddr (...) * select (std).* from ( * select standardize_address( * 'lextab', 'gaztab', 'rultab', micro, marco) as std * from table_to_standardize) as foo; * * The structure of the lextab and gaztab tables of views must be: * * seq int4 * word text * stdword text * token int4 * * the rultab table or view must have columns: * * rule text
The problem on linux is that every other time I run the command I get and error, This is probably because I'm not zero something out when I delete an item from the cache.
test1=# select * from standardize_address('lex'::text, 'gaz'::text, 'rules'::text, '123 Main Street'::text, 'Kansas City, MO 45678'::text);
building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
----------+-----------+--------+------+---------+------+---------+--------+------------+-------+-------------+----------+---------+----------+-----+------
| 123 | | | | MAIN | STREET | | | | KANSAS CITY | MISSOURI | | 45678 | |
(1 row)
test1=# select * from standardize_address('lex'::text, 'gaz'::text, 'rules'::text, '123 Main Street'::text, 'Kansas City, MO 45678'::text);
ERROR: AddStdHashEntry: This memory context is already in use! (0xb89d9d08)
test1=# select * from standardize_address('lex'::text, 'gaz'::text, 'rules'::text, '123 Main Street'::text, 'Kansas City, MO 45678'::text);
building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
----------+-----------+--------+------+---------+------+---------+--------+------------+-------+-------------+----------+---------+----------+-----+------
| 123 | | | | MAIN | STREET | | | | KANSAS CITY | MISSOURI | | 45678 | |
(1 row)
test1=# select * from standardize_address('lex'::text, 'gaz'::text, 'rules'::text, '123 Main Street'::text, 'Kansas City, MO 45678'::text);
ERROR: AddStdHashEntry: This memory context is already in use! (0xb8e6b4a8)
It should be easy to find and fix. I also need to run this in songle user mode under valgrind to see if I have any memory leaks.
comment:28 by , 11 years ago
Checked in a fix for the last problem svn revision 353.
Here is my download and build script:
wget -O pagc-postgresql.tgz 'http://pagc.svn.sourceforge.net/viewvc/pagc/branches/sew-refactor/postgresql/?view=tar' rm -rf postgresql tar xzf pagc-postgresql.tgz cd postgresql export PATH='/c/ming32/projects/gettext/rel-gettext-0.18.1/bin:/c/ming32/projects/xsltproc:/c/ming32/projects/gtk/bin:/c/ming32/projects/rel-libiconv-1.13.1w32/include:.:/bin:/include:/mingw/bin:/mingw/include:/c/Windows/system32:/c/Windows:/usr/local/bin:/c/ming32/Silksvn/bin::/c/ming32/projects/pgx32/pg92w32/bin:/c/ming32/projects/pgx32/pg92w32/lib' make SHLIB_LINK="-L/c/ming32/msys/local/lib -Wl,--enable-stdcall-fixup -lpostgres -lpgport -lwsock32 -lm -lws2_32 -lshfolder -lpcre" CPPFLAGS=-I/usr/local/include && make install createdb test1 psql -c 'create extension address_standardizer;' test1 psql -f test.sql test1 psql -f test2.sql test1
You may need to edit test.sql can change the path to the extension directory so that the files lex, gax, and rules tables will load.
comment:29 by , 11 years ago
| Resolution: | → fixed |
|---|---|
| Status: | new → closed |
Great that works well on my EDB 64-bit install now and also in my pagc_normalize wrapper. The speeds are about the same now, though I suspect yours with a larger set would outperform the tiger built in one.
testpostgis210=# SELECT address, pagc_normalize_address(address)
testpostgis210-# FROM test_parse;
SELECT address, pagc_normalize_address(address)
FROM test_parse;
address | pagc_normalize_address
-----------------------------------------------------+-------------------------------------------------------
529 Main Street, Boston MA, 02129 | (529,,MAIN,St,,,Boston,MA,02129,t)
77 Massachusetts Avenue, Cambridge, MA 02139 | (77,,MASSACHUSETTS,Ave,,,Cambridge,MA,02139,t)
25 Wizard of Oz, Walaford, KS 99912323 | (25,,"WIZARD OF",,,"# OZ WALAFORD","KS 99912323",,,t)
26 Capen Street, Medford, MA | (26,,CAPEN,St,,,Medford,MA,,t)
124 Mount Auburn St, Cambridge, Massachusetts 02138 | (124,,"MOUNT AUBURN",St,,,Cambridge,MA,02138,t)
950 Main Street, Worcester, MA 01610 | (950,,MAIN,St,,,Worcester,MA,01610,t)
949 N 3rd St, New Hyde Park, NY, 11040 | (949,N,3,St,,,"New Hyde Park",NY,11040,t)
8401 W 35W Service Dr NE, Blaine, MN 55449 | (8401,W,"35 W","Svc Dr",NE,,Blaine,MN,55449,t)
(8 rows)
Time: 106.295 ms
address | normalize_address ----------------------------------------------------+------------------------------------------------- 529 Main Street, Boston MA, 02129 | (529,,Main,St,,,Boston,MA,02129,t) 77 Massachusetts Avenue, Cambridge, MA 02139 | (77,,Massachusetts,Ave,,,Cambridge,MA,02139,t) 25 Wizard of Oz, Walaford, KS 99912323 | (25,,"Wizard of Oz",,,,Walaford,KS,99912323,t) 26 Capen Street, Medford, MA | (26,,Capen,St,,,Medford,MA,,t) 124 Mount Auburn St, Cambridge, Massachusetts 02138 | (124,,"Mount Auburn",St,,,Cambridge,MA,02138,t) 950 Main Street, Worcester, MA 01610 | (950,,Main,St,,,Worcester,MA,01610,t) 949 N 3rd St, New Hyde Park, NY, 11040 | (949,N,3rd,St,,,"New Hyde Park",NY,11040,t) 8401 W 35W Service Dr NE, Blaine, MN 55449 | (8401,W,35W,"Svc Dr",NE,,Blaine,MN,55449,t) 8 rows) ime: 100.177 ms
testpostgis210=# SELECT address, normalize_address(address) FROM test_parse;
SELECT address, normalize_address(address) FROM test_parse;
address | normalize_address
-----------------------------------------------------+-------------------------------------------------
529 Main Street, Boston MA, 02129 | (529,,Main,St,,,Boston,MA,02129,t)
77 Massachusetts Avenue, Cambridge, MA 02139 | (77,,Massachusetts,Ave,,,Cambridge,MA,02139,t)
25 Wizard of Oz, Walaford, KS 99912323 | (25,,"Wizard of Oz",,,,Walaford,KS,99912323,t)
26 Capen Street, Medford, MA | (26,,Capen,St,,,Medford,MA,,t)
124 Mount Auburn St, Cambridge, Massachusetts 02138 | (124,,"Mount Auburn",St,,,Cambridge,MA,02138,t)
950 Main Street, Worcester, MA 01610 | (950,,Main,St,,,Worcester,MA,01610,t)
949 N 3rd St, New Hyde Park, NY, 11040 | (949,N,3rd,St,,,"New Hyde Park",NY,11040,t)
8401 W 35W Service Dr NE, Blaine, MN 55449 | (8401,W,35W,"Svc Dr",NE,,Blaine,MN,55449,t)
(8 rows)
Time: 100.177 ms
I have one minor gripe that your function is not schema aware which required me to strip off the tiger schema in my input function. I'll ticket that as a separate issue but wil go ahead and change my wrapper function for now.
comment:30 by , 11 years ago
I'm going to keep this closed, but I'm still seeing an issue with performance. I suspect it might be the way I'm wrapping your functions rather than anything wrong in your functions.
I have a table of about 500,000 records of boston residents and I pulled a random 100 (well not that random but).
and if I run this:
{{{ — takes 60 ms on hot, 120 ms on cold
| ' ' | COALESCE(streetname,) | ' ' | city | ', ' | state | ' ' | zip) |
FROM electionstime_2012 limit 100;
— takes 100 ms cold, 40 ms hot — — why your standardize_address seems faster — than parse_address is a mystery as I would expect parse to be faster — I have to say this is after changing your function from set returning — but I recall speed being about the same SELECT (std).*
| ' ' | COALESCE(streetname,), city | ', ' | state | ' ' | zip) As std from electionstime_2012 |
limit 100) As a;
— but my function which granted its doing some stuff — should be spending most of its time parsing and then standardizing — takes a whopping 2714 ms
| ' ' | COALESCE(streetname,) | ' ' | city | ', ' | state | ' ' | zip) from electionstime_2012 |
limit 100;
— versus - takes 618 ms
| ' ' | COALESCE(streetname,) | ' ' | city | ', ' | state | ' ' | zip) from electionstime_2012 |
limit 100; }}}
So possibly the cost of copying to norm_addy structure I am underestimating or something else I am doing wrong.
comment:31 by , 11 years ago
Code got mangled
-- takes 60 ms on hot, 120 ms on cold
SELECT parse_address(COALESCE(streetno,'') || ' ' || COALESCE(streetname,'') || ' ' || city || ', ' || state || ' ' || zip)
FROM
electionstime_2012
limit 100;
-- takes 100 ms cold, 40 ms hot --
-- why your standardize_address seems faster
-- than parse_address is a mystery as I would expect parse to be faster
-- I have to say this is after changing your function from set returning
-- but I recall speed being about the same
SELECT (std).*
FROM (SELECT standardize_address('pagc_lex','pagc_gaz', 'pagc_rules', COALESCE(streetno,'') || ' ' || COALESCE(streetname,''), city || ', ' || state || ' ' || zip) As std from electionstime_2012
limit 100) As a;
-- but my function which granted its doing some stuff
-- should be spending most of its time parsing and then standardizing
-- takes a whopping 2714 ms
SELECT tiger.pagc_normalize_address(COALESCE(streetno,'') || ' ' || COALESCE(streetname,'') || ' ' || city || ', ' || state || ' ' || zip) from electionstime_2012
limit 100;
-- versus - takes 618 ms
SELECT tiger.normalize_address(COALESCE(streetno,'') || ' ' || COALESCE(streetname,'') || ' ' || city || ', ' || state || ' ' || zip) from electionstime_2012
limit 100;
comment:32 by , 11 years ago
I think I know what is wrong. I think I'm still not taking advantage of your cache since your cache is a per query cache. I was thinking since your function is a subcall of mine, it would be considered part of the same call, but I realize now each one is probably being treated as a separate call still even though its now part of a larger query. Not sure how to get out of that. I'll open a separate ticket for that as its not really your problem and an issue of how I'm wrapping it.
Of course if you could somehow change this to a per prcoess backend cache rather than per query cache, that would solve the million calls per webservice issue as well as my problem
comment:33 by , 11 years ago
What do you mean that my function is not schema aware?
I have not tried it by I would think that you should be able to pass 'tiger.lex', etc for the table names and it should work. Oops, I just remembered that disallow special characters. I'll allow the '.'. Just checked in svn rev 354.
comment:35 by , 11 years ago
confirmed its being recreated for each call I have. I'm going to try to rewrite as an sql function instead of plpgsql and then I think it might fold into be the main query
comment:36 by , 11 years ago
| Resolution: | fixed |
|---|---|
| Status: | closed → reopened |
gosh darn it. Switching it to pure sql did not fix the issue. Still considers each call a separate query and creates a std object for each.
Normally this would be fine except to completely swap out I have to merge parse_address and standardize_address into a single query and that seems like a lot to ask of people.
{{{ — if I write the equivalent query like this: WITH p AS (
SELECT parse_address(address) As var_parse_rec FROM test_parse)
SELECT (to_number(substring( (var_rec).house_num, '[0-9]+'), '99999999999')
,trim( (var_rec).predir)
, trim((var_rec).name)
,trim(COALESCE((var_rec).suftype, (var_rec).pretype))
, trim((var_rec).sufdir) , trim((var_rec).unit) ,trim((var_rec).city) , trim((var_rec).state) , (var_rec).postcode , true)::norm_addy FROM (
SELECT standardize_address('pagc_lex'
, 'pagc_gaz' , 'pagc_rules'
, COALESCE((var_parse_rec).address1,),
COALESCE((var_parse_rec).city ', ',) COALESCE((var_parse_rec).state ' ', ) COALESCE((var_parse_rec).zip,) ) As var_rec FROM p) AS s;
}}}
Takes 46 ms vs.
CREATE OR REPLACE FUNCTION tiger.pagc_normalize_address2(in_rawinput character varying)
RETURNS norm_addy AS
$$
SELECT (to_number(substring( (var_rec).house_num, '[0-9]+'), '99999999999')
,trim( (var_rec).predir)
, trim((var_rec).name)
,trim(COALESCE((var_rec).suftype, (var_rec).pretype))
, trim((var_rec).sufdir)
, trim((var_rec).unit)
,trim((var_rec).city)
, trim((var_rec).state)
, (var_rec).postcode
, true)::norm_addy
FROM (
SELECT standardize_address('pagc_lex'
, 'pagc_gaz'
, 'pagc_rules'
, COALESCE(var_parse_rec.address1,''),
COALESCE(var_parse_rec.city || ', ','') || COALESCE(var_parse_rec.state || ' ', '') || COALESCE(var_parse_rec.zip,'') ) As var_rec
FROM parse_address($1) As var_parse_rec ) AS s
$$
LANGUAGE sql
COST 100;
{{{ — takes 156 ms (6 times as long) SELECT address, pagc_normalize_address2(address) FROM test_parse; }}}
The main issue , to be able to do a drop in replace, people just pass the address as a single field, so you I need a version of the standardizer that does both a parse and standardize in one call so people don't have to struggle with a complicated query. This might be a good thing to have anyway as I imagine a lot of people will have the addresss as a single field.
Then all my function would need to do is define a cast between std and norm_addy or I'd just write a geocode function that takes std instead of norm_addy.
I'm reopening this since I think its hard for me to fix without changes to pagc_address_parser, and will be an issue for others wanting to wrap the function to wrap it efficiently.
comment:37 by , 11 years ago
Correction only 4 times as long, but I think it gets proportionately worse the more records in my set.
comment:38 by , 11 years ago
Okay the good news is the most trivial of functions I can come up with is wrappable (dissolves into the main plan), but doesn't seem to allow higher than this:
CREATE OR REPLACE FUNCTION pagc_standardize(micro text,macro text) RETURNS stdaddr AS
$$
SELECT standardize_address('pagc_lex'
, 'pagc_gaz'
, 'pagc_rules'
, $1, $2 ) AS std ;
$$
language 'sql';
The bad news it has to be like that. As soon as I try to return as an OUT param or try to throw the parse address in there, PostgreSQL refuses to fold into the main plan and I loose the caching.
comment:39 by , 11 years ago
I started looking into writing a function:
standardize_address('lex', 'gaz', 'rules', 'address')
and there is at least one issue to be delta with. parse_address() will take the form "street1 @ street2 city state zip" which is for an intersection and this would need to be standardized into two separate records. This would then require throwing an error or changeing the function to be set returning which would require the caller to branch on whether it get 1 or 2 records in the result.
If it would be useful to have a convenience function like the above that throws an error if it is passed an intersection, it would be pretty easy to all that.
comment:40 by , 11 years ago
New function checked into version 359.
standardize_address('lex', 'gaz', 'rules', 'address')
This can be optimized a little by adding the state hash to the query cache also, but I suspect that it is not needed unless this is getting used to standardize multiple million row tables. Anyway see if this helps before we optimize further.
comment:41 by , 11 years ago
Getting warmer. For first phase this should do fine, but still can't do as a drop in replace for the main function without losing speed unfortunately so it still requires different calling for users to achieve same speed because as soon as I try to incorporate it in a plpgsql function, I lose the cache and while I can hide some of the plumbing in SQL functions and CASTS, an SQL function has both the beauty and ugliness of being transparent. The transparency hurts during install because postgres tries to validate it and will fail if address_standardizer is not installed So to hide it in SQL functions I have to require the pagc to be installed or wrap it in a bridge extension. None of which is all that appealing.
comment:42 by , 11 years ago
| Resolution: | → fixed |
|---|---|
| Status: | reopened → closed |
done enough. I still need to package the windows binaries though.
here is one of my quick benchmark tests:
DROP TABLE IF EXISTS test_parse; CREATE TABLE test_parse(addid serial PRIMARY KEY, address text); INSERT INTO test_parse(address) VALUES ('529 Main Street, Boston MA, 02129'), ('77 Massachusetts Avenue, Cambridge, MA 02139'), ('25 Wizard of Oz, Walaford, KS 99912323'), ('26 Capen Street, Medford, MA'), ('124 Mount Auburn St, Cambridge, Massachusetts 02138'), ('950 Main Street, Worcester, MA 01610') , ('949 N 3rd St, New Hyde Park, NY, 11040') , ('8401 W 35W Service Dr NE, Blaine, MN 55449');— pagc one
But pagc one is in debug mode so not a fair test.