Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Environment variables in Postgres C extension

Trenton

I cannot get environment variable in my PostgreSQL C extension code.

For example, this function always returns 111:

#include "postgres.h"
#include "fmgr.h"
#include 
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(myinc);

Datum

myinc(PG_FUNCTION_ARGS)

{

int32 arg = PG_GETARG_INT32(0);

char* envar = getenv("MYINC");

if (envar) {

PG_RETURN_INT32(arg + atoi(envar));

} else {

PG_RETURN_INT32(111);

}

}

While this C program works as expected, it prints whatever MYINC is:

#include 
#include 
int main()

{

printf("MYINC: %s", getenv("MYINC"));

return 0;

}

T
tahishae last edited by

That should work just fine. Note, however, that it will take the variable from the environment of the PostgreSQL server process, not your current client process.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

R

Postgres caching and bytea
SQL, Database Testing • postgresql cache • • Rossere

2

0
Votes

2
Posts

0
Views

I think the first question is how much RAM the system has. If you have enough that the database fits comfortably in RAM then this is not a major problem. Basically the OS disk cache is very fast, and if the db is sitting there effectively, then you don't have anything to worry about. If you are on a system with, say, 4GB RAM and not a whole lot else running, I wouldn't worry. The problem happens when this is not true, and then the shared buffers try to hold the most frequently used information. I would expect that caching would be ok there too. What you have to remember is that the PostgreSQL cache is slower than the OS cache because it provides features aimed at keeping frequently used data in the cache. Of course all of the above depends highly on your query patterns.
E

In postgre), I want to convert yyymmddHHMM-format into 'MMMM-MM-DD HH:MI' character type.
Software Programming • postgresql • • esther

2

0
Votes

2
Posts

0
Views

https://www.postgresql.jp/document/12/html/functions-formatting.html date stringSecond argument string formatconvert timestamp to type.So once the string isYYYYMMDDHH24MIafter re timestamp type in format,to_charHomeSecond argument string formatyou need to process two steps to convert to a string.select to_char(to_timestamp('202007101010', 'YYYYMMDDHH24MI'), 'YYYY-MM-DD HH24:MI')
J

How to pass environment variables to a sql file from bash shell (PostgreSQL)
SQL, Database Testing • postgresql bash psql • • juvenalb

2

0
Votes

2
Posts

0
Views

T

This will allow you to do it. In your case, everything between '$$' is a "dollar-quoted string constant, so nothing gets interpreted. See sql syntax, 4.1.2.4. \set CREDO 'begin create user ' :dbuser '; exception when duplicate_object then raise notice ''user already exists''; end'; do :'CREDO';
C

How to get index scan for OR'ed time-range predicates?
SQL, Database Testing • postgresql index query performance postgresql performance range types • • Cleofas

2

0
Votes

2
Posts

1
Views

Expression index A GiST or (even better) SP-GiST expression index on an inclusive timestamp range should work wonders. CREATE INDEX events_right_idx ON events USING spgist (tsrange(time_start, time_end, '[]')); Rewrite your query with the "range contains" operator @> ``` and match the indexed expression (exactly equivalent to your original): SELECT user_id FROM events WHERE tsrange(time_start, time_end, '[]') @> timestamp '2021-08-24 15:30:00' OR tsrange(time_start, time_end, '[]') @> timestamp '2021-08-24 15:59:00' GROUP BY user_id; You'll get a query plan like this: HashAggregate (cost=9.90..10.00 rows=10 width=4) Group Key: user_id -> Bitmap Heap Scan on events (cost=2.57..9.88 rows=10 width=4) Recheck Cond: ((tsrange(time_start, time_end, ''[]''::text) @> ''2021-08-24 15:30:00''::timestamp without time zone) OR (tsrange(time_start, time_end, ''[]''::text) @> ''2021-08-24 15:59:00''::timestamp without time zone)) -> BitmapOr (cost=2.57..2.57 rows=10 width=0) -> Bitmap Index Scan on events_right_expr_idx (cost=0.00..1.28 rows=5 width=0) Index Cond: (tsrange(time_start, time_end, ''[]''::text) @> ''2021-08-24 15:30:00''::timestamp without time zone) -> Bitmap Index Scan on events_right_expr_idx (cost=0.00..1.28 rows=5 width=0) Index Cond: (tsrange(time_start, time_end, ''[]''::text) @> ''2021-08-24 15:59:00''::timestamp without time zone) ``` Should be much faster. By default, range types assume inclusive lower and exclusive upper bound (tsrange(time_start, time_end) - equivalent to tsrange(time_start, time_end), '[)'). Since you operate with >= and <=</code> <em><strong>include</strong></em> both bounds with <code>tsrange(time_start, time_end, '[]')</code>.</p> <p>Related:</p> <ul> <li><a href="https://stackoverflow.com/a/22111524/939860">Perform this hours of operation query in PostgreSQL</a></li> <li><a href="https://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns/39599#39599">Optimizing queries on a range of timestamps (two columns)</a></li> </ul> <h3>Alternatively, store range column in table</h3> <p>Should be a bit faster, yet, as plain (not expression) index.<br /> You can add a timestamp range column to your table, like:</p> ALTER TABLE event ADD COLUMN ts_range tsrange GENERATED ALWAYE AS (tsrange(time_start, time_end, '[]')) STORED; <p>See:</p> <ul> <li><a href="https://stackoverflow.com/a/8250729/939860">Computed / calculated / virtual / derived columns in PostgreSQL</a></li> </ul> <p><strong>Or</strong>, more radically, replace <code>time_start</code> and <code>time_end</code> with the range column. Then index and query are a bit simpler:</p> CREATE INDEX events_right_idx ON events USING spgist (ts_range); SELECT user_id FROM events WHERE ts_range @> timestamp '2021-08-24T15:30:00' OR ts_range @> timestamp '2021-08-24T15:59:00' GROUP BY user_id; <p>But the <code>tsrange</code> column occupies more space than two <code>timestamp</code> columns. Weigh cost and benefits.</p> <h3>Asides</h3> <p>Postgres 14 (currently beta) even allows a covering SP-GiST index. <a href="https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.5.5.3.5" rel="nofollow noreferrer">The release notes:</a></p> <blockquote> <p>Allow SP-GiST to use INCLUDE'd columns (Pavel Borisov)</p> </blockquote> <p>But I don't think you can get index-only scans for your particular query.</p> <p>If you have to make do with your B-tree index for some reason, this fixed <code>UNION</code> query shouldn't be too bad:</p> SELECT user_id FROM events WHERE '2021-08-24T15:30:00' BETWEEN time_start AND time_end UNION SELECT user_id FROM events WHERE '2021-08-24T15:59:00' BETWEEN time_start AND time_end <p>Notably, no <code>GROUP BY</code>. <code>UNION</code> already does all the work.<br /> And simplify with <code>BETWEEN</code> (no effect on performance).</p> <p>Also, you seem to have a wild mix of <code>timestamp without time zone</code> and <code>timestamp with time zone</code>. And name it "time" to add to the confusion. Typically <code>timestamptz</code> is the better choice. See:</p> <ul> <li><a href="https://stackoverflow.com/a/9576170/939860">Ignoring time zones altogether in Rails and PostgreSQL</a></li> </ul> <p>Last, but not least, this indicates inaccurate column statistics, leading to a sub-optimal query plan:</p> <pre> -> Parallel Seq Scan on events (cost=0.00..242553.74 <b>rows=45952</b> width=4) (actual time=104.085..183.994 <b>rows=64</b> loops=3) </pre> <p>Run</p> ANALYZE events; <p>And retry. Your original query <em>can</em> use a plain B-tree index. It's just not as efficient as the suggested SP-GiST index.<br /> And then maybe tune your <code>autovacuum</code> and statistics settings to avoid bad statistics in the future. See:</p> <ul> <li><a href="https://dba.stackexchange.com/questions/260429/aurora-postgresql-database-using-a-slower-query-plan-than-a-normal-postgresql-fo/260432#260432">Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?</a></li> <li><a href="https://stackoverflow.com/a/8229000/939860">Keep PostgreSQL from sometimes choosing a bad query plan</a></li> </ul>
D

A tool to convert PostgreSQL database dumps between versions?
SQL, Database Testing • postgresql • • Duquan

2

0
Votes

2
Posts

0
Views

G

Are there tools or options to create a pg_dump file that is compatible to earlier versions of PostgreSQL No there are no options or tools to create a backwards compatible pg_dump files. Depending on how far back the version the data needs to be ported to the dump file may work there is no guarantee A work around this limitation would be run pg_dump using the -P --plain and --schema-only modify the resulting SQL file to make it compatible. Restore the file. Then run pg_dump again using -P --plain and -a --data-only options then restore the file.
G

Serial ID not assigning to the model id
Software Programming • php postgresql yii2 mvc • • guilherme

2

0
Votes

2
Posts

0
Views

R

Your model->save() has to come before $model->uploadImage Why $id still doesn't exist while this model is not saved in the database. Your code reorganized to work: $model->ofe_emp_id = Yii::$app->user->identity->usu_emp_id; $model->ofe_cliques = 0; $model->ofe_titulo = $_POST['ofe_titulo']; $model->ofe_sub_id = $_POST['subcategoria']; $model->ofe_tipo_id = $_POST['ofe_tipo_id']; $model->ofe_redir = $_POST['ofe_redir']; $model->save(); $conteudoDaImagem = Yii::$app->request->post('imagem'); if ($model->uploadImagem($conteudoDaImagem) || $conteudoDaImagem){ return $this->redirect(['view', 'id' => $model->ofe_id]); }else{ return $this->redirect(['index']); }
R

How postgres deals with XIDs and wraparounds?
SQL, Database Testing • postgresql transac languagetion • • rielyn

2

0
Votes

2
Posts

0
Views

F

Imagine the transaction IDs from 0 to 4294967296 in a circle. AS some point in that circle is your current transaction ID. Let's say your current transaction ID is 1000000. Then the 2^31 transaction IDs after it are in the future: 1000001 to 2148483648. And the 2^31 transactions before that are the past: 2148483649 to 4294967296 and 0 to 999999 (remember we have a circle, so right after 4294967296 comes 0). Once your current transaction ID crosses 4294967296, it becomes 0 again (to be precise, it will become 3, because the transaction IDs 0 to 2 have a special meaning).
R

C API hangs in mysql_query()
SQL, Database Testing • mysql mysql 5.7 myisam • • Raziyah00

2

0
Votes

2
Posts

0
Views

C

Are you doing a backup every 12 hours? MyISAM is blocked when dump, ALTER, OPTIMIZE, and several other operations are done. Switch to InnoDB to eliminate most of such locks. You can probably see the offending SQL by getting a full SHOW PROCESSLIST;.
A

Using sub-query column in where clause
SQL, Database Testing • postgresql • • Amritpal

2

0
Votes

2
Posts

0
Views

You can't reference an alias on the same "level" where it was defined. You need to wrap your base query into a derived table: select * from ( select u.*, l.*, (select count(cr.id) from cloud_recordings cr left join cameras c on c.owner_id=u.id where c.id=cr.camera_id) valid_licence from users u left join licences l on l.user_id=u.id ) t where valid_licence > 0;
Does anyone know how I do this effect in the background of div?
Software Programming • c language • • briley

2

0
Votes

2
Posts

0
Views

N

The image that allows you to make this done quadriculated is a point only, with some transparency: https://static.musiocdn.com/img/dot-overlay.png?45a8ea7a65f7 It is put on top as overlay<div class="dot-overlay"></div> And it is above the original image in repetition based on the default value of background-repeat it is repeat. This value forces repetition both in height and in width giving the idea of a grid.Replicating this effect:.fundo { background-image:url(http://c1.staticflickr.com/3/2912/13981352255_fc59cfdba2_b.jpg); background-size:cover; width:100%; height:300px; } .overlay { background-image:url(https://static.musiocdn.com/img/dot-overlay.png?45a8ea7a65f7); width:100%; height:100%; }<div class="fundo"> <div class="overlay"> </div> </div>The "shadow" that indicates is actually a black background with 50% transparency over it. You can incorporate this in a number of ways.Here's an example for this too:.fundo { background-image:url(http://c1.staticflickr.com/3/2912/13981352255_fc59cfdba2_b.jpg); background-size:cover; width:100%; height:300px; position:relative; } .overlay { position:absolute; background-image:url(https://static.musiocdn.com/img/dot-overlay.png?45a8ea7a65f7); width:100%; height:100%; } .sombra { position:absolute; width:100%; height:100%; background: rgba(0,0,0,0.5); }<div class="fundo"> <div class="overlay"></div> <div class="sombra"></div> </div>You can even use one linear-gradient if you want to be more creative.
A

Postgres 10 and partitioning by week
SQL, Database Testing • partitioning psql • • amarantha

2

0
Votes

2
Posts

0
Views

C

main manual doesn't help me a lot. I hope you've read the manual, both entries: ddl_partitioning (& the Limitations 5.10.2.3. Limitations) create table If yes, please mention specifically what you don't understand about it. partitioning natively... but what does it mean? You don't have to create check constraints, trigger functions or triggers. The SQL example above needs to be modified thusly: e07=# CREATE TABLE public.audit_logs ( log_type varchar(31) NOT NULL, id serial NOT NULL , additional_data varchar(1024) NULL DEFAULT NULL::character varying, week int NOT NULL DEFAULT date_part('week', CURRENT_TIMESTAMP) ) PARTITION BY list (week) ; --catch this error in the code: --ERROR: no partition of relation "audit_logs" found for row and how to create auto tables public.audit_logs_N, where N would be week number? Depending on the requirements, create the partitions in advance or use a pgagent (or crontab) to create them one at a time in the prior week. create table audit_logs_not_1 partition of audit_logs for values in (1); ... create table audit_logs_not_53 partition of audit_logs for values in (53); Do i need to create a trigger? or what? A trigger is only needed for Implementation Using Inheritance
D

Remove duplicate comparisons in postgres?
SQL, Database Testing • postgresql distinc languaget • • Duquan

2

0
Votes

2
Posts

0
Views

G

You can use least() and greatest() for this: select distinct least(s1.id, s2.id), greatest(s1.id, s2.id) from scale_polygons_v4 s1 join scale_polygons_v4 s2 on s1.id <> s2.id and s1.kind = s2.kind and st_overlaps(s1.geom,s2.geom); Another option is to do this in the join condition: select distinct s1.id, s2.id from scale_polygons_v4 s1 join scale_polygons_v4 s2 on s1.id > s2.id and s1.kind = s2.kind and st_overlaps(s1.geom,s2.geom);
Y

When to run Vacuum Full
SQL, Database Testing • postgresql postgresql 12 • • yajahira

2

0
Votes

2
Posts

1
Views

K

Some cases where I would do VACUUM FULL: The system is about the run out of disk space and fall over. And that system is dominated by a very bloated table, but without bloated indexes. This should be followed up with some navel gazing about how we get into this mess in the first place. Or, I fixed what was causing the bloat, now I want to restore the table to a nonbloated state for (hopefully) the last time. Or, I took over a bloated system, no one from the ancien regime knows why. I want to debloat it and watch it rebloat to make it easier to figure out what is going on. If you only have bloated indexes, it would be better to REINDEX, not VACUUM FULL. On the most recent versions, this can be done concurrently.
J

why do they still support all those different versions of postgresql?
SQL, Database Testing • postgresql • • Jolied

2

0
Votes

2
Posts

0
Views

A

They have a 5 Year end of time policy see homepage That is why 9.5 has got the latest official update in February You have to check the manual what mandatory sql standard and optional standards which version supports or in case of Windows for example which Version of Windows it is supported. manuals
A

Declaring and using variables in triggers
SQL, Database Testing • mysql trigger • • Amritpal

2

0
Votes

2
Posts

0
Views

As per my comment above, the chances are that you have a foreign key that is set to delete on cascade. This is what is causing your lookup to fail. I hope this helps you.

Environment variables in Postgres C extension

Suggested Topics

Postgres caching and bytea SQL, Database Testing • postgresql cache • • Rossere

In postgre), I want to convert yyymmddHHMM-format into 'MMMM-MM-DD HH:MI' character type. Software Programming • postgresql • • esther

How to pass environment variables to a sql file from bash shell (PostgreSQL) SQL, Database Testing • postgresql bash psql • • juvenalb

How to get index scan for OR'ed time-range predicates? SQL, Database Testing • postgresql index query performance postgresql performance range types • • Cleofas

A tool to convert PostgreSQL database dumps between versions? SQL, Database Testing • postgresql • • Duquan

Serial ID not assigning to the model id Software Programming • php postgresql yii2 mvc • • guilherme

How postgres deals with XIDs and wraparounds? SQL, Database Testing • postgresql transac languagetion • • rielyn

C API hangs in mysql_query() SQL, Database Testing • mysql mysql 5.7 myisam • • Raziyah00

Using sub-query column in where clause SQL, Database Testing • postgresql • • Amritpal

Does anyone know how I do this effect in the background of div? Software Programming • c language • • briley

Postgres 10 and partitioning by week SQL, Database Testing • partitioning psql • • amarantha

Remove duplicate comparisons in postgres? SQL, Database Testing • postgresql distinc languaget • • Duquan

When to run Vacuum Full SQL, Database Testing • postgresql postgresql 12 • • yajahira

why do they still support all those different versions of postgresql? SQL, Database Testing • postgresql • • Jolied

Declaring and using variables in triggers SQL, Database Testing • mysql trigger • • Amritpal

Postgres caching and bytea
SQL, Database Testing • postgresql cache • • Rossere

In postgre), I want to convert yyymmddHHMM-format into 'MMMM-MM-DD HH:MI' character type.
Software Programming • postgresql • • esther

How to pass environment variables to a sql file from bash shell (PostgreSQL)
SQL, Database Testing • postgresql bash psql • • juvenalb

How to get index scan for OR'ed time-range predicates?
SQL, Database Testing • postgresql index query performance postgresql performance range types • • Cleofas

A tool to convert PostgreSQL database dumps between versions?
SQL, Database Testing • postgresql • • Duquan

Serial ID not assigning to the model id
Software Programming • php postgresql yii2 mvc • • guilherme

How postgres deals with XIDs and wraparounds?
SQL, Database Testing • postgresql transac languagetion • • rielyn

C API hangs in mysql_query()
SQL, Database Testing • mysql mysql 5.7 myisam • • Raziyah00

Using sub-query column in where clause
SQL, Database Testing • postgresql • • Amritpal

Does anyone know how I do this effect in the background of div?
Software Programming • c language • • briley

Postgres 10 and partitioning by week
SQL, Database Testing • partitioning psql • • amarantha

Remove duplicate comparisons in postgres?
SQL, Database Testing • postgresql distinc languaget • • Duquan

When to run Vacuum Full
SQL, Database Testing • postgresql postgresql 12 • • yajahira

why do they still support all those different versions of postgresql?
SQL, Database Testing • postgresql • • Jolied

Declaring and using variables in triggers
SQL, Database Testing • mysql trigger • • Amritpal